The IF function

The IF function
You can use the IF function to make Excel select from a pair of possible outputs based on whether the
inputs to the function meet your specified criterion or not.
For instance, you might take a range of temperature measurements over several days. Having recorded
this data in a spreadsheet, you could then use an IF function to ask Excel to decide whether a day should
be called ‘hot’ or ‘cold’ depending on the temperatures you have recorded.
Parameters for the IF function
Parameters are things that a function needs in order to return a valid answer.
The IF function has three parameters:
The logical test: this is a question that the IF function asks of a piece of data – it must have a
yes/no answer
The true response: this is what will happen if the IF function gets a ‘yes’ answer to the logical test
The false response: this is what will happen if the IF function gets a ‘no’ answer
These parameters are entered in brackets, separated by commas. They must be entered in the above
order or Excel will not understand the function.
Constructing an IF function
Using the example of determining whether a day was ‘hot’ or ‘cold’ depending on the temperature
measured in that day, we can construct an IF function so that Excel can test each piece of data.
We can put an IF function in column C to test the data in the cells of column B to decide whether the
weather is hot or cold.
A day will be considered hot if the temperature exceeds 25°C.
This cell will display ‘hot’
if the temperature
entered in cell B3 is over
25°C and ‘cold’ if it is
below that temperature
The logical test used in this example is B2>25 – Excel uses this to ask the question “is the value in cell B2
more than 25?”
If the answer to this question is “Yes, it’s more than 25”, then the cell will say ‘hot’ – this is the true
If the value is “No, it is not more than 25”, the cell will return the false response, and say ‘cold’.
To return a word as the answer to your question, you must put speech marks around the word or it will not
display properly.
The logical test asks if the
value in B2 is more than 25
If the answer is yes,
the cell will say hot
If the answer is no,
the cell will say cold
are separated
by commas
Speech marks indicate
that the result will be text
rather than a number
For this cell, the IF function
has returned cold, because
the temperature in cell B2 is
below 25°C
We can then use auto fill to add this function to the other cells in column C. The reference in the function is
a relative cell reference, so it always refers to the cell immediately to the left of the function rather than
always referring to cell B2. This means that each calculation we auto fill will be unique for the temperature
value in each row.
For this cell, the IF function
has returned hot, because
the temperature in cell B8 is
above 25°C
Logical tests
The logical tests used in IF functions are usually constructed using comparison operators. These
compare two values and give a yes or no answer depending on the result of the comparison.
This table shows the comparison operators you can use to construct a logical test in Excel:
Not equal to
Greater than
Less than
Greater than or equal to
Less than or equal to
You can use these to compare the value in a cell to an absolute value or to a value from another cell.
In the example above, we have used an absolute value, comparing the cell contents to see if they are
greater or less than 25.
We could use a relative value to compare the cells to one another; for instance instead of deciding whether
a day was hot or cold, we could have asked whether it was warmer or colder than the day before. An
example function for this would be:
Returning the result of a calculation
You can build an IF function that returns the result of a calculation rather than an absolute value.
To return the result of a calculation, you can put calculations rather than values or text into the true and
false responses.
For example, if we have purchased some stationery and there is a sales tax of 15% that only applies to
items costing over £1, we can set up an IF function which will calculate the amount of tax payable on each
We would like this cell to
display a tax of 15% if the
item in this row costs more
than £1, and zero otherwise
The IF function to calculate this would be:
The logical test asks if the
value in B2 is more than 1
If the condition in the logical test is met,
the tax will be calculated for that item
If the condition is not met,
the cell will display zero
Here we have set a condition that the cost of the item be more than £1, and said that if it is, we will
calculate 15% of the cost of the item by multiplying the cost by 0.15. If the condition is not met, we will
return zero instead.
Auto filling for the rest of
the column returns the tax
value for each of the items,
and zero where there is no
tax charged
Nesting IF functions
A nested IF function is one which is used within another IF function in place of one of the responses.
You may find that you need to pose more than one question before you determine which output you would
like to produce. To do this, you can use a nested set of IF functions to refine the results of the logical test
to give a more accurate answer.
This means that you can use a series of yes/no questions to ask questions which have more sophisticated
To return to our earlier example, we have said that days where the temperature is over 25°C are hot, and
those below that temperature are cold. We could refine that to say that days over 25°C are hot, those
below 10°C are cold, and anything in between we will consider to be warm.
To achieve this, we need to ask two yes/no questions before we can give an answer:
Is the temperature above 25°C?
Is the temperature below 10°C?
The function we will use to do this is:
A second IF function is
used in place of the false
response – it is nested
within the first IF function
The results for the nested
function will determine what will
happen if the condition is not
met for the first logical test
Excel will colour code both the cell references and the brackets for nested functions to make it easier to
complete all your functions.
Excel uses colour coding
for both brackets and cell
references for nested
Using nested functions, we
can return three different
responses using yes/no
You can nest up to 64 IF functions within Excel 2010 to build highly refined results.