Applications Session 9 - Advanced Excel formulas, Functions and Conditional formatting

I know I said that we would continue with Powerpoint, but I though we would come back to Powerpoint if we have time at the end. Today we will look at Advanced Excel. I am assuming that you all know the basics of Excel, so while today's lesson is not hard, it is assuming some prior knowledge.

Advanced Formulas

All of the sample files from today's lesson are available from Moodle. Please download from Course Blog under the Learning Resources section.

Logical Functions

Nesting Ifs

 Have a look at the following spreadsheet.

A reminder of what the IF function looks like and the components required.

The top one is a simple If function with a calculation. In this case, our logical test will be whether the savings are less that $4000. If the amount is less than $4000, the condition will be true and the rate would be 2%. If the amount is not less than $4000, then condition is false (the amount must be $4000 or higher), which will display the rate of 5%.

=IF(A8<4000,2%,5%)



N.B. You can do the same function by placing the interest rates in cells on your spreadsheet and using a cell reference for the interest rates.

We can also NEST an IF Function. Nesting means that for the true or false activity, there is another IF function. The second example uses a nested IF function.

EXERCISE 1

Complete EXAMPLE 1 and EXAMPLE 2 in the 2017LogicalFunctionsIf spreadsheet.

AND Function

The AND function is used to compare more than one condition. It returns TRUE only if all of the conditions are met, and takes the format:

=AND(condition1, condition2,…)

For example, you could use the following formula:

=AND(B2 > 400, C2 < 300) where,

B2 > 400 is the first condition being tested

C2 < 300 is the second condition being tested

This will only return the result TRUE if the value in cell B2 is greater than 400 and the value in cell C2 is less than 300. In all other situations, the result will be FALSE.

EXERCISE 2

Complete PART A and PART B in the 2017LogicalFunctionsAnd spreadsheet.

The OR Function

The OR function is also used to compare more than one condition. It returns TRUE if any of the conditions are met, and takes the format:

=OR(condition1, condition2,…)

For example, you could use the following formula:

=OR(B2 > 400, C2 < 300) where,

B2 > 400 is the first condition being tested

C2 < 300 is the second condition being tested

This will return the result TRUE if either the value in cell B2 is greater than 400 or the value in cell C2 is less than 300. The result will be FALSE only if neither of the conditions are met.

EXERCISE 3

Complete PART C in the 2017LogicalFunctionsAnd spreadsheet.

LOOKUP FUNCTIONS

Choose

The lookup function we will use is the CHOOSE function. The CHOOSE function is designed to allow you to make a choice from a list of items. The format of this function is:

CHOOSE(lookup-value, list of items)


Use the spreadsheet 2017LOOKUPFunctionsChoose

To use the CHOOSE function, each of our values need to be given a name, not the whole data table.
Click on the first rate – cell B24 in the example and in the name box, type rate1. Call B25 rate2, B26 rate3, B27 rate4.

The CHOOSE Function will be:

=CHOOSE(C4,rate1, rate2, rate3, rate4)

Have fun nesting one or more of these functions together.

COUNTIF, SUMIF, AVERAGEIF

The functions Sum, Average and Count can be used to provide statistical information about a range of data.

These functions also have a counterpart that allows you to incorporate conditions. Use the spreadsheet 2017StatisticalIf

Count how many have a branch of Cranbourne: =COUNTIF(B4:B29,"Cranbourne")

This is what the SUMIF function looks like

EXERCISE 4

Using 2017StatisticalIf spreadsheet perform the following:

• Sum the Q1 values that are over $5000
• Sum the values in Q1 that are not Frankston.
• Count how many are Dandenong
• Count how many reps earned more than $5000 in Qtr 1
• Total the sales for Cranbourne

Multiple Conditions

Use the SUMIFS, COUNTIFS, AVERAGEIFS to have multiple conditions that are to be tested.

Count how many Reps in Cranbourne that earned over $5000 in Qtr 1

EXERCISE 5

Count of Reps in Dandenong who earned a total of more than $20000
Count of Reps who earned more than $5000 in ALL Quarters
How many of the above were Dandenong?

Conditional Formatting

Data Bars

In later versions of Excel (2007 - 2013), under conditional formatting, you can create an inline chart. I personally think these are cool!! For those visual people, you can see the data as a number as well as represented as a chart bar.

Use the same spreadsheet - 2017StatisticalIf

Highlight the values in Qtr 1. Under conditional Formatting and Data Bars. Hover over one of the buttons and see what it does.

I know, right!! AWESOME isn't it. Just a little thing, but it can mean a lot more than just the dollar figures when it comes to comparing - how easy is it now to see where the larger amounts are???

EXERCISE 6

Create Data Bars for the Total column in the 2017StatisticalIF spreadsheet

Color Scales

Another way to be more visual with your data is to use Color Scales. Rather than creating a bar, your cells are coloured based on the values, making it easier to find the lower amount and/or the high amounts.

For the default color scale, Excel calculates the median or middle value.  That cell is coloured yellow. The cell that holds the minimum value is coloured red and the cell that holds the maximum value is coloured green. All other cells are coloured proportionally around these.


If you want to set your own scale and/or rules for formatting, you can.

EXERCISE 7

Create a colour scale for the total column in the 2017StatisticalIf spreadsheet with the midpoint being 25000

Icon Sets

These work in a similar way to the color scales except that icons are used rather than the cells being coloured.

If you just want to see the icons and not the values, you can check the box Show Icons Only.

With a 3 icon set, Excel, by default will take the maximum and minimum values and create 3 percentiles for the amounts in between. Again, you can change the rules to suit yourself.

Applications Session 8 - Advanced Powerpoint

Is there such a thing?

Powerpoint is really such an easy application to use, that even at the "Advanced" end, it is still nice and easy. From one end of the spectrum to the other!

I am assuming that you all know a bit about PowerPoint and how to create a presentation and apply a theme to make a plain presentation look really swish, so today I will show you how to create your own theme.

Creating a Template/Theme

Start by loading PowerPoint using a Blank Presentation.

To be able to create or change a Theme, you need to change the view to Slide Master. You can access this from the View tab.

Your presentation area will now contain the "Master" versions of all of your slides.

Once you are in Master Slide View, you can customise and create your own Template. Many organisations will have their own template for presentations - this allows a consistent look and feel within the organisation. Chisholm has one that staff can download from our Intranet and use for any Chisholm Presentations.

This is what the Slide Master View ribbon looks like. It gives you access to a range of tools that can be used to create/modify Slidemaster templates, slide layout, colours, and more.

Start by deciding which elements you want on each slide - things like date and slide number

You can  then play with the Slide Layouts and placeholders.

To create a nice background, you can add an image, shapes, gradient, or solid colour. Make sure you are at the top of the tree in the slide navigation pane.

You can start by changing the background colour. Use the various tools within the Background section of the ribbon. You can have a solid colour, a gradient, or even an image as a background. With images, you can control the transparency to allow for a nice subtle background.

Make sure that you are on the top slide if you want all of the slides to have the same stuff on it.

In this sample, I used a solid background and placed some shapes on it to give it a bit of interest. To make sure that the shapes are behind any text, you can use the send backwards option.

To see what your new theme looks like, just close the SlideMaster view

Saving your Template/Theme

You have two options when it comes to saving your new Master Template.You can save it as a PowerPoint Template, or you can save it as a Theme. Both options are OK, but will have two different ways of using your design.

To save as a PowerPoint Template, go to File / Save As and Choose PowerPoint Template as the file type (.potx)

To use the template, you can double click the file to open it or you start PowerPoint and select the template from your custom templates.

Alternatively, you can save your design as a Theme.

To save as a Theme, Select Save Current Theme (.thmx)

The main difference is how you use the design for a presentation. A template is a file that is opened and you see the theme straight away. A theme is applied to a blank presentation.

Useful Formatting Tools

When you are creating your theme, remember some of  the tools that will help you!!

Transparent Colour in Images

Remove Background

Grouping Shapes

Bringing Forward or sending Backwards

This will change the order of the items in a stack.

The following example has a starburst behind a shape. See what happens when it gets moved forward.

Here is a sample template for Mysty River Regional Library Corporation.

ASSESSMENT TASK

Create your own PowerPoint Theme for a fictitious library or if you don't have one, for Mysty River Regional Library Services.

Create a template/Theme that is suitable for your organisation

If you would like to see the full Assessment Guidelines, please click here