Chapter 6 Conditional Logic in Excel

Microsoft Excel 2007 to 2013 - The IF Function

The IF function can be quite useful in a spreadsheet. It is used when you want to test for more than one value. For example, has a bill been paid or not? If it has, you can deduct the amount from the money you have left to spend; if it hasn't, keep it on your debt list. Later, you'll see how to use the IF Function to grade student exam scores. If the student has above 80, award an A grade; if the student has below 30, award a fail grade. First, here's what an IF Function looks like:

IF(logical_test, value_if_true, value_if_false,)

The thing to note here is the three items between the round brackets of the word IF. These are the arguments that the IF function needs. Here's what they mean:

logical_test

The first argument is what you want to test for. Is the number in the cell greater than 80, for example?

value_if_true

This is what you want to do if the answer to the first argument is YES. (Award an A grade, for example)

value_if_false

This is what you want to do if the answer to the first argument is NO. (Award a FAIL grade.)

If that's not terribly clear, an example may clear things up. Open a new spreadsheet, and do the following:

• Widen the B column a bit, as we'll be putting a message in cell B1
• Now click in cell A1 and type the number 6
• Type the following in the formula bar (The right angle bracket after A1 means "Greater Than".)

=IF(A1 > 5, "Greater than Five", "Less than Five")

Hit the enter key on your keyboard and your spreadsheet should look like ours below:

(Make sure you have all the commas and double quotes in the correct place, otherwise Excel will give you an error message. That right angle bracket ( > ) is known as a Conditional Operator. You'll meet some others shortly.)

But what we're saying in the IF function is this:

logical_test: Is the value in cell A1 greater than 5?

value_if_true: If the answer is Yes, display the text "Greater than Five"

value_if_false: If the answer is NO, display the text "Less than Five"

So your first tell Excel what you want to check the cell for, then what you want to do if the answer is YES, and finally what you want to do if the answer is NO. You separate each part with a comma.

Exercise

Try this:

• Click into cell A1
• Change the 6 into a 4
• Hit the enter key on your keyboard

What happens?

Exercise

Now type the number 5 in cell A1. What happens now?

For the last exercise above, Excel should tell you that 5 is "Less than 5"! It does this because the answer to your logical test was NO. We were testing if the number in cell A1 was greater than 5. Since 5 is not greater than 5, the answer to the question is NO. We've told Excel to display a message of "Less than 5", if the answer was NO. In other words, we didn't tell Excel what to do if the value in cell A1 was the same as 5.

The solution to this is to use a different Conditional Operator. We used the Greater Than ( > ) operator. Here's some more:

<      Less Than

>=    Greater than Or Equal To

<=    Less than Or Equal To

<>    Not Equal To

For the second and third operators above, you type an angle bracket followed by the equals sign. There are no spaces between the two. For the final one, it's a left angle bracket followed by a right angle bracket.

So for our exercise, the symbol we should have used was the one for Greater than Or Equal To. Change your IF function to this and try again:

=IF(A1 >= 5, "Greater than or Equal to Five", "Less than Five")

Exercise

Test the A1 cell to see if the value is less than or equal to 5. If it is, display a suitable message. If it's not, display the message "Greater than Five".

Complex If Functions

The If Functions you've just met are consider fairly simple ones. They can get really complex!

Consider our Student Exam problem. The spreadsheet we created to track our students looks like this, from an earlier section:

However, we want to display the following grades as well:

A If the student scores 80 or above

B If the student scores 60 to 79

C If the student scores 45 to 59

D If the student scores 30 to 44

FAIL If the student scores below 30

With such a lot to check for, what will the IF Function look like? Here's one that works:

=IF(B2>=80, "A", IF(B2>=60, "B", IF(B2>=45, "C", IF(B2 >=30, "D", "Fail" ) ) ) )

Quite long, isn't it? Look at the colours of the round brackets above, and see if you can match them up. What we're doing here is adding more IF Functions if the answer to the first question is NO. If it's YES, it will just display an "A".

But take a look at our Student Exam spreadsheet now:

After the correct answer is displayed in cell B14 on the spreadsheet above, we used AutoFill for the rest!

Don't worry if that long IF statement is making your brain hurt - it is quite complicated.

Conditional Formatting

You can use something called Conditional Formatting in your Excel spreadsheets. Conditional Formatting allows you to change the appearance of a cell, depending on certain conditions. What we'll do is to colour the Overall Averages on our Student Exam spreadsheet, depending on the grade. Here's the spreadsheet we'll be working on.

• Open up your Student Exam spreadsheet (You did complete it, didn't you?)
• Highlight the cells with Overall Grades, which should be cells B11 to I11

The Overall Averages range from 44 to 85. We'll colour each grade, depending on a scale. A different colour will apply to the following grades:

• 50 and below
• 51 to 60
• 61 to 70
• 71 to 80
• 81 and above

So five different bands, and a colour for each. To set the Conditional Formatting in Excel, do the following:

• With your Overall Averages highlighted, click on the Home menu at the top of Excel
• Locate the Styles panel, and the Conditional Formatting item:

The Conditional Formatting menu gives you various options. The easiest one is the Colour Scales option. Select one of these and Excel will colour the cell backgrounds for you:

That's not quite what we're looking for, though. We'd like to choose our own values. So click on More Rules, from the Colour Scales submenu. You'll see the following rather complex dialogue box:

The one we want is the second option, Format only cells that contain. This will allow us to set up our values. When you click this option, the dialogue box changes to this:

The part we're interested in is the bottom part, under the heading Edit the Rule Description. It says Cell Value and Between, in the drop down boxes. These are the ones we want. We only need to type a value for the two boxes that are currently blank in the image above. We can then click the Format button to choose a colour.

So type 0 in the first box and 50 in the second one:

Then click the Format button. You'll get another dialogue box popping up. This is just the Format Cells one though. You've met this before. Click on the Fill tab and choose a colour. Click OK and you should see something like this under Edit the Rule Description:

The Preview is showing the colour we picked. So we've said, "If the Cell Value is between 0 and 50 then colour the cell Red".

Click OK on this dialogue box to get back to Excel. You should find that one of the cells has turned red. To format the rest of the cells, click on Conditional Formatting on the Styles panel again. From the menu, click on Manage Rules:

You'll get yet another complex dialogue box popping up! This one:

Our first rule is already there - Cell Value Between. The only thing we're doing here is adding New Rules, similar to the one we've just set up. Click the New Rule button then. You'll see the exact same dialogue boxes you used to set up the first rule. Set a new colour for the next scores - 51 to 60. Choose a colour, and keep clicking OK until you get back to the Rules Manager dialogue box. It should now look something like this one:

We now have to colours in our range. Do the rest of the scores, choosing a colour for each. The scores are these, remember:

• 50 and below
• 51 to 60
• 61 to 70
• 71 to 80
• 81 and above

When you've done them all, your dialogue box should have five colours:

The colours above are entirely arbitrary, and you don't have to select the same ones we did. The point is to have a different colour for each range of scores. But click OK when you're done. Your Overall Averages will then look something like this:

Formatting your spreadsheet in this way allows you to see at a glance relevant information. In the spreadsheet above, it's obvious who's failing - just look for the red cells!

CountIF in Excel

Another useful function that uses Conditional Logic is CountIF. This one is fairly straightforward. As its name suggests, it counts things! But it counts things IF a condition is met. For example, keep a count of how many students have an A Grade.

To get you started with this function, we'll use our Student Grade spreadsheet and count how many students have a score of 70 or above. First, add the following label to your spreadsheet:

As you can see, we've put our new label at the start of the K column.

We can now use the CountIF function to see how many of the students scored 70 or above for a given subject.

The CountIF function looks like this:

COUNTIF(range, criteria)

The function takes two arguments (the words in the round brackets). The first argument is range, and this means the range of cells you want Excel to count. Criteria means, "What do you want Excel to look for when it's counting?".

So click inside cell K2, and then click inside the formula bar at the top. Enter the following formula:

=CountIf(B2:I2, ">= 70")

The cells B2 to I2 contain the Math scores for all 8 students. It's these scores we want to count.

Press the enter key on your keyboard. Excel should give you an answer of 4:

(If you're wondering where the columns B to I have gone in the image above, we've hidden then for convenience sake!)

To do the rest of the scores, you can use AutoFill. You should then have a K column that looks like this:

By using CountIF, we can see at a glance which subjects students are doing well in, and which subjects they are struggling in.

Exercise

Add a new label to the L column. In the cells L2 to L9, work out how many students got below 50 for a given subject. You should get the same results as in the image below:

SumIF

Another useful Excel function is SumIF. This function is like CountIf, except it adds one more argument:

SUMIF(range, criteria, sum_range)

Range and criteria are the same as with CountIF - the range of cells to search, and what you want Excel to look for. The Sum_Range is like range, but it searches a new range of cells. To clarify all that, here's what we'll use SumIF for. (Start a new spreadsheet for this.)

Five people have ordered goods from us. Some have paid us, but some haven't. The five people are Elisa, Kelly, Steven, Euan, and Holly. We'll use SumIF to calculate how much in total has been paid to us, and how much is still owed.

So in Column A, enter the names:

In Column B enter how much each person owes:

In Column C, enter TRUE or FALSE values. TRUE means they have paid up, and FALSE means they haven't:

Add two more labels: Total Paid, and Still Owed. Your spreadsheet should look something like this one:

In cells B10 and B11, we'll use a SumIF function to work out how much has been paid in, and how much is still owed. Here's the SumIF function again:

SUMIF(range, criteria, sum_range)

So the range of cells that we want to check are the True and False values in the C column; the criteria is whether they have paid (True); and the Sum_Range is what we want to add up (in the B column).

In cell B10, then, enter the following formula:

=SUMIF(C3:C7, TRUE, B3:B7)

When you press the enter key, Excel should give you the answer:

So 265 is has been paid in. But we told SumIF to first check the values in the cells C3 to C7 (range). Then we said look for a value of TRUE (criteria). Finally, we wanted the values in the B column adding up, if a criteria of TRUE was indeed found (sum_range).

Exercise

Use SumIF to work out how much is still owed. Put your answer in cell B11.

We'll leave the subject of Conditional Logic. In the next section, more things you can do with Excel!