Introduction

Spreadsheets, and technology in general for that matter, can be fantastic. Until that is, things don’t go as expected. It’s all too easy for an entire month’s productivity gains to be lost when sorting out a single spreadsheet problem. In this series, we will be looking at some examples of Excel not doing what we expect and at how to deal with the problems.

So far, we have looked at the basic problem of our numbers not adding up and looked at solving the problem using Excel’s ROUND() function before warning about the use of the Precision as Displayed option and examining the issues of ranges that don’t include all the cells that they should. Last time we looked at another cause of mysterious maths issues – numbers that are not always numbers (but are sometimes…)

This time, we move on from addition issues to the use of the IF() function.

What if?

We finished the previous post in the series by showing how TRUE/FALSE values can be used to multiply other values by 1 or 0 as an alternative to using the IF() function:

This time we will investigate IF() in a bit more detail before we briefly consider another reason why numbers in Excel don’t always behave themselves.

The IF() function is often used to help automate processes in Excel. Instead of manually changing calculations to cope with changing values we can use the IF() function to make the change for us automatically. For example, you could use it, as we have done in our example, to apply a particular calculation when a value reaches or exceeds a threshold.

The IF() function takes three arguments. The first is a value or calculation that evaluates to a TRUE or FALSE value, with values of zero being treated as FALSE and all non-zero values evaluating to TRUE. The second argument is the value that is returned as the function result when the first argument evaluates to TRUE and, unsurprisingly, the third argument is the value returned when the first argument evaluates to FALSE. The first argument is often entered as a ‘statement’ containing a comparison, such as the value in G9 is greater than the value in G3:

=IF(G9>$G$3,$G$4,0)

If the statement is true, the value of the second argument will be returned, otherwise the third argument would be returned.

However, the first argument doesn’t need to be a comparison statement in this way, it can just be a direct reference to a value. In the example below, our IF() function just refers to a single value held in a cell. If that value is FALSE or 0, the third argument is returned. For non-zero numbers, whether they be positive, negative or TRUE, the second argument will be used.

Because we just need the equivalent of a TRUE or FALSE value for our first argument, there is usually no need to compare something to zero. Here, we have added another column with IF() functions that compare our value to see if it is not equal (less than or greater than) to zero:

=IF(A13< />0,"Second","Third")

As you can see, the results are the same as when just referring to the values in column A directly, with one exception: our comparison statement finds that FALSE is not equal to 0. This might seem strange given that zero values are treated as FALSE, but it is due to the issue that we covered last time, number coercion. Unless we coerce FALSE to become a numeric value, it remains as a Boolean value that is distinct from the number 0.

In addition to the methods for coercing values to be treated as numbers that we saw last time, we can employ the rather strange technique of using two minuses in front of our value. The first minus is one of the arithmetic operators that can coerce a Boolean value to its numeric equivalent, and the second minus cancels out the first operation of making a plus a minus or vice versa, so that we don’t end up with a number with the wrong sign:

=IF(--A17< />0,"Second","Third")

Our Boolean value of FALSE is now coerced to 0 and our formula will return the value of the third argument.

HOWZAT

The first IF() argument is not limited to looking at a single comparison or value, we can use the AND() function to check whether a series of values are all TRUE; the OR() function to check whether one or more of a series of values is TRUE and the NOT() function to make a TRUE value FALSE or a FALSE value TRUE. Here is an example of using IF() to see if a batsman is out LBW (leg before wicket) in a game of cricket. We have used Excel Range names to refer to the cells that contain the individual TRUE and FALSE values that we need to evaluate:

=IF(OR(NOT(Hitting),Edge,OutsideLeg,AND(NOT(HitInLine),
PlayingStroke)),"NOT OUT","OUT")

IF() plus

There are other Excel functions that use similar comparison techniques to those we have just looked at. As well as IFS() and SWITCH() for working with multiple possible criteria, the main aggregate functions have ‘if’ variants to calculate totals based on whether one or more statements is true, for example: SUMIFS(), COUNTIFS(), AVERAGEIFS(), MAXIFS() and MINIFS().

When 0.10 doesn’t equal 0.10

Finally, we’ll look at a reason why the use of IF() doesn’t might not give you the answer that you expect. In the example below, it looks as though our IF() function in row 21 believes that 0.10 does not equal 0.10:

However, if we increase the number of decimal points in each of our values, we can see that they are indeed not equal. We have generated the value of 0.10 in cell A22 using the simple formula:

=1.2-1.1

Because of the way that ‘floating point arithmetic’ works in Excel, this is calculated as being very close, but not exactly equal, to 0.10, as we can see when we display (lots) more decimal points. This problem can also occur with simple rounding errors. As a result, if we want two numbers to be considered equal as long as they are just equal to a given number of decimal points, we should include the ROUND() function as part of our IF() statement:

=IF(ROUND(A23,2)=ROUND(B23,2),"Equal","Not equal")

Next time

Before concluding our ROUND() circle from the first article in this series, next time we will see how rounding issues can also affect number formats, and how to make a dash for it when this happens.

Additional resources

• You can explore all aspects of Excel, including the full range of IF() and related functions, in the ICAEW archive