Microsoft Excel Functions: IF

The IF function is a powerful function that can generate an answer based on set criteria

IF tests a criteria and returns a value if the test is true, and another value if the test is false.

Learn how to use the IF function to help cut down on the amount of time you spend analysing data. Once you have set the criteria for the function it will generate the specified output based on the answer to a question being true or false.

Download the accompanying file(s) for this article.

Work through the below guide as we learn how the IF statement works

=IF

First, a little bit of education. The first time you see an IF statement in Excel you may be tempted to close the file down and do something else. The syntax is really quite simple to understand:

=IF(logical test, value if true, value if false), or in plain English =IF(what you want to test, answer if yes, answer if no)

Example

Breaking down the IF Formula

=IF: This is the start of the statement

(C6=>10: This is saying C6 is greater than 10 (logical test)

,1: Return 1 (value if true)

,2: Return 2 (value if false)

What we are saying here is, if cell C6 has a value greater than 10 return the value 1, if the value is less than 10, return the value 2

Why is this useful?

Using =IF is a very quick way to determine an answer given a set of criteria. It can be something simple such as if a student’s test score is higher than 90% award them an ‘A’ to a complex statement adding lots of different cells together to arrive at an answer.

If you are likely to want to change the value being tested, for example, change the value from >10 to >15 in this workbook, I would advise linking the test value to a cell, then making your formula absolute to the cell.

This approach allows you to change one cell within a worksheet that can then change the calculation for thousands of cells. Think how much time it would take to re-write the formula and check that it is correct across all the cells. This approach saves time, effort and gives the correct output.

Try changing the values in the highlighted cells in the accompanying workbook to see how much easier it is to work with an IF formula when you link the test and outputs to cells.

You can supercharge your IF formulas by using a ‘Nested IF’ statement. This allows you to perform a multitude of tests on a single value. A tutorial on Nested-Ifs will follow later.

If you’ve found this article interesting and or useful please share it on social media using the handy share buttons

Subscribe to our mailing list to be the first to know when new articles are published

At Happy Duck we build bespoke business and financial management tools for small, medium and large companies that help them achieve their goals. This makes us happy. Find out more about us and about our services.

More in Powerful Formula

0 Comments

Leave a comment

Your e-mail address will not be published. Required fields are marked *