Introduction to using IF in Excel

All too often the spreadsheet application Excel is used to produce professional looking tables of information. All very good, but, professional looking tables can also be created in Word - something Word was intended as well as other word processing tasks.  The real power of Excel lies in its ability to carry out mathematical operations from the most basic of adding two numbers together to performing complex statistical operations.

Excel has a number (well over 200) built in functions for the user.  An example of a simple function is;

=MAX(A1:A10)

What this function does is really quite logical.  It looks at all the values in range of cells A1 to A10 (10 cells in all) and displays the highest value.

The IF function is also a simple but very useful function, however, how it is used is often confusing to the new Excel user.  In a nutshell what it does is to perform a test and, if that test is true do something but, if the test is false, do something else.  The IF function is constructed as follows;

=IF(Logical test, What to do if test is true, What to do if test is false)

So, inside the (  ) there are three parts separated by commas,  The first part is the test to perform, the second part is what Excel should if the test is true and the final third part is what Excel should do if the test is false.

A simple IF function could look like this;

=IF(A1>=10,"More than or equal to ten","Less than ten")

In the above example the 'test' is to see if the value in cell A1 is greater than or equal to 10 (> means greater than) and, if this test is true (for example if the the value in A1=12) then display the text "More than or equal to ten".  If the value in A1 is less than 10, the text "Less than ten" will be displayed.

Of course, the 'what to do' bits could be calculations like this;

=IF(A1>=10,A1*1.5,A1*2)

Of course, this is just the start.  You can 'nest' functions (ie put functions inside other functions) to perform even more complex tasks.  For example;

=IF(MAX(A1:A5)>=10,"Maximum value is equal to or more then 10","")

In the above example, Excel looks at the values in the range of cells A1 to A5 and if the maximum value in this range is greater than or equal to 10, it displays the text "Maximum value is equal to or more then 10".  If all values are less than 10, then display nothing (hence the empty "") in the third part of the IF function.