CCNC/CCNC Module 4/Working with Formulas and Functions/Using the logical function IF

Using the logical function IF

(: A portion of this sub-section was copied from 2 sections of Free Computer Tutorials, namely Conditional Logic and the CountIF function)

The logical IF function allows you to state what happens depending on whether certian conditions are met. It is therefore also referred to as conditional logic. There are two words that are key to the use of this function - IF and THEN. You use this if-then type of logic all the time, even though you may not realise it. For example, you might say to yourself, "IF I eat this delicious cream cake THEN my diet will be ruined." You're using conditional logic to make a decision: IF I do this THEN that will happen. Here are some more examples:

IF I buy this lovely coat THEN I will look beautiful

IF I watch one programme THEN I can tape the other

IF I win the lottery THEN I will be happy

Those are all examples of conditional logic. Calc also uses the IF word to set up a condition. You can test what is in a cell, and say what should happen if it is one value rather than another. For example, suppose cell A1 has the number 6 in it. In cell A2, you can enter an IF function to test whether cell A1 is above 5 or below 5. IF it is above 5 THEN one thing happens; IF it is below 5 THEN another thing happens.

The correct format to use for the IF function is this:

IF(logical_test, value_if_false, value_if_true)

So the IF function takes three arguments: logical test, value if true, value if false. Let's break those three arguments down a little more:

Logical Test

This is what you want to test for. In our example, we wanted to test whether cell A1 is greater than or less than 5. Calc uses this symbol > for greater than and this symbol < for less than. So for the first argument, we would put A1 > 5

Value If True

This is the THEN part of the IF statement. Calc needs to know what you want to happen IF your condition is met. You can put in text surrounded by quotes, or another formula.

Value If False

You also need to say what should happen IF your condition is not met.

Let's clarify all this with a spreadsheet example. So, start a new spreadsheet and do the following:

• Click inside cell A1 and enter the number 6
• Press the return key on your keyboard
• Widen the B column of your spreadsheet.
• Click inside cell B1
• Click inside the formula bar

The entry you make here will be the content of the cell B1

• Enter the following formula:

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

Press the return key on your keyboard

Your spreadsheet should look like this one below:

SCREENSHOT HERE

Tip: You must get all the syntax right in your IF function. In other words, don't forget the commas, and the double quote marks around text.

Now click inside cell A1 and change the number 6 to the number 4. Then press the return key on your keyboard. Calc should display the phrase Less than Five in cell B1. Now put the number 5 in cell A1 and see what happens.

Nothing happened, right? That's because the IF function is very precise. You didn't tell Calc what to do if the number equalled 5.

To get round this, we can use the Greater Than ( > ) symbol and the Equal symbol together. Like this:

A1 >= 5

That says "A1 greater than or equal to 5". When we amend our function, it looks like this:

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

Following are the the main expressions used to set up a condition:

= is equal to <> is not equal to > is greater than >= is greater than or equal to < is less than <= is less than or equal to

So to sum up: after typing IF you tell Calc what you want to test for. Then you say what should happen if the condition is true. Then you say what should happen if the condition is false.

CountIF

A special IF function is the CountIF function. It is based on the Count function we met earlier and only includes things when a certain condition is met. For example, only count the students whose grades are above B.

It uses this syntax:

COUNTIF(range, criteria)

In other words, it takes two arguments. The first argument is range, and this means the range of cells you want to count. The second argument is the criteria that you want Calc to look for when it is counting. Let's see it in action, using the data given below. Open a spreadsheet and enter the data as given below. For this demonstration, we will be using the Maths scores only, which are the contents of B2:I2.

SCREENSHOT FROM HOME AND LEARN TUTORIAL

Now do it

• Click inside cell B15
• Click inside the formula bar
• Enter the following function:

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

Press the return key on your keyboard The answer you should get is 4

The formula says, look in cells B2 to I2. Then check for a score of greater than or equal to 70. If you find this score, keep a Count.

So 4 students achieved a mark of 70 or above for Maths. To do the rest of the scores, you can use AutoFill. When you have finished, it should look like this one below:

SCREENSHOT FROM HOME AND TUTORIAL

CountIF is a very useful function for simple data analysis.

Combining the IF function and cell referencing

In the previous examples, the results we generated using the IF function was based on data drawn from contained in a single set of cells, either a single row or a single column. There are times however, when the data may be drawn from a cell range.

(: The content of this subsection needs to carefully reviewed.)

Suppose in the previous example, we wished to indicate whether a score is above average or below average by inserting the appropriate word in the adjacent column. We could proceed as follows:

1. Selecting column C 2. Insert->Column 3. Enter the following in C9 =IF(B9>B$7; “Above”; “Below”)

Notice the mixed mode addressing in this example. The reason for this is that the value being compared to is always in the same row 7.

4. Copy the contents of C9 to C10:C39

To understand why we need mixed mode addressing rather than absolute addressing in C9, suppose we were to insert a column after column D and copy column C to the new column. We would get the following:

If we had used absolute addressing, you would then have obtained the entry =IF(D9>$B$7;”Above”;”Below”) when you copied cell B9 to E9. However the average for column D is found in D7 not in B7.