Excel Tip of the Week #66 - IF revisited

Hello all and welcome back to the Excel Tip of the Week.  In this week's General User post, we are revisiting a topic from all the way back in TOTW #17: IF functions.

I’ll start by taking a little time to go back over the basics (you might want to check out the example workbook) and then will move it forward, looking at nested IFs and using IF in combination with other functions.

The Formula: IF is a logical function.  The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE.

How to write one: The syntax for an IF formula looks like this:
=IF(Logical Test, Value if True, Value if False)
For example:
=IF(Actual figure < budget figure, “Within Budget”, “Over Budget”)

Logical Test – any expression that can be evaluated to true or false. For example, checking whether the value in one cell is higher (or lower) than that of another.  See below for more detail.

Value if True – what value you want the function to return if the answer to the logical test is true (or yes). This can be a number, text (written within “”, e.g. “PASS”), a cell reference, or even a further formula (but more on that later).

Value if False – as above, but this time the value you want returning if the answer to the test is false (or no).

Logical Test options

Because IF is a logical function, you have certain arguments you can use within the test. As a rule, if a question can be answered with “True” or “False” then that question can be asked within an IF function. Think of the “Logical Test” section of the formula as a statement that you are checking.

The options (known as “Comparison Operators”) are:

=          Equal to
>          Greater than
<          Less than
>=         Greater than or equal to
<=         Less than or equal to
<>         Not equal to

Put these between the two references (for this example, just two cells) you want to compare i.e. A1<>B1 for "Are A1 and B1 different?".

Nesting

It is possible to double up the arguments you are asking in the formula (or even go past this – Excel allows up to 64 IF statements in one formula!) by returning a new IF function as the value for either true or false in an argument. This is called “nesting” and can be very useful when used correctly.

It can be tempting, however, to create a very long formula with a series of IF statements. This leaves a much higher margin for error, and makes the formula itself less transparent and user friendly. If you find yourself nesting a lot of IF statements together, there is generally a simpler and cleaner solution within Excel to evaluate the data.  For example, try looking at functions such as AND, CHOOSE, INDEX, MATCH, LOOKUP – some of these are covered in past TOTW posts, and others are coming soon!

Nested IF statements are useful if you are looking between ranges of numbers or dates – for example looking at grading test results – see the worked example attached above.

A bit further

With careful use of brackets, you can compare results of formulae against each other, for example:

( (A1-B1) > (C1-D1) ) will check if the total of A1 minus B1 is less than the total of C1 less D1

( (SUM(A1:A10) ) = (B1) ) will check if the sum of A1 to A10 equals the value in cell B1.

Providing you keep asking logical questions, and put brackets in the right place (very important!) you can build a formula up to ask exactly the questions you want answering.

An aside… you’ll note how above I've used the space bar to space out my formulae. You can do this in Excel! Providing the start of the formula (i.e. =SUM( …) has no spaces, once you are writing you can space your formula out a bit more. Just remember, each function name needs an open bracket directly after it, no spaces. This is a purely aesthetic point, but it can be very useful for reviewing longer formulae. If you’re at all unsure though, go without – it’s easy to break a formula this way!

Please bear in mind that Excel deals in absolutes. If you’re checking two numbers against each other (i.e. A1=B1), they may look identical to you but an IF formula will give you a FALSE result.  This is because a number has to be exactly equal for it to count as a True result.  So, if you are displaying numbers to two decimal places, but the underlying number runs into more, you’re going to get false negative results.  If you are using IF for error checks, this can be quite annoying!  A good way round this is to build in a tolerable variance, so that rather than looking if two cells are identical you instead check that any difference between them is lower than your tolerable variance. There's a worked example of this in the second page of the attached workbook.

Previous post - Merge, Wrap, Autofit
TOTW index
Next post - Automatic vs. manual calculation

Thanks to Emma Horton for her input on this Tip.

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.