MULTI IN ONE: The IF Function

<div dir="ltr" style="text-align: left;" trbidi="on">
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The IF function can be quite useful in a spreadsheet. It is used when you want to test for more than one value. For example, has a bill been paid or not? If it has, you can deduct the amount from the money you have left to spend; if it hasn't, keep it on your debt list. Later, you'll see how to use the IF Function to grade student exam scores. If the student has above 80, award an A grade; if the student has below 30, award a fail grade. First, here's what an IF Function looks like:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>IF(<span style="color: red;">logical_test</span>, <span style="color: blue;">value_if_true</span>, <span style="color: #006600;">value_if_false</span>,)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The thing to note here is the three items between the round brackets of the word IF. These are the arguments that the IF function needs. Here's what they mean:</div>
<dl style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<dt><b><span style="color: red;">logical_test</span></b></dt>
<dd>The first argument is what you want to test for. Is the number in the cell greater than 80, for example?</dd>
<dt><b><span style="color: blue;">value_if_true</span></b></dt>
<dd>This is what you want to do if the answer to the first argument is YES. (Award an A grade, for example)</dd>
<dt><b><span style="color: #006600;">value_if_false</span></b></dt>
<dd>This is what you want to do if the answer to the first argument is NO. (Award a FAIL grade.)</dd></dl>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
If that's not terribly clear, an example may clear things up. Open a new spreadsheet, and do the following:</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Widen the B column a bit, as we'll be putting a message in cell B1</li>
<li>Now click in cell A1 and type the number 6</li>
<li>Type the following in the formula bar (The right angle bracket after A1 means "Greater Than".)</li>
</ul>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=IF(<span style="color: red;">A1 > 5</span>, "<span style="color: blue;">Greater than Five</span>", "<span style="color: #006600;">Less than Five</span>")</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Hit the enter key on your keyboard and your spreadsheet should look like ours below:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="An Excel 2007 IF Statement" border="1" height="93" src="http://www.homeandlearn.co.uk/excel2007/images/2IfStatements.gif" width="553" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
(Make sure you have all the commas and double quotes in the correct place, otherwise Excel will give you an error message. That right angle bracket ( > ) is known as a Conditional Operator. You'll meet some others shortly.)</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
But what we're saying in the IF function is this:</div>
<span class="TextIndent3" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif; margin-left: 5em;"><span style="color: red;">logical_test</span>: Is the value in cell A1 greater than 5?</span><br style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;" /><span class="TextIndent3" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif; margin-left: 5em;"><span style="color: blue;">value_if_true</span>: If the answer is Yes, display the text "Greater than Five"</span><br style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;" /><span class="TextIndent3" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif; margin-left: 5em;"><span style="color: #006600;">value_if_false</span>: If the answer is NO, display the text "Less than Five"</span><br />
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
So your first tell Excel what you want to check the cell for, then what you want to do if the answer is YES, and finally what you want to do if the answer is NO. You separate each part with a comma.</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>Exercise</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Try this:</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Click into cell A1</li>
<li>Change the 6 into a 4</li>
<li>Hit the enter key on your keyboard</li>
</ul>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
What happens?</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /><b>Exercise</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Now type the number 5 in cell A1. What happens now?</div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
For the last exercise above, Excel should tell you that 5 is "Less than 5"! It does this because the answer to your logical test was NO. We were testing if the number in cell A1 was greater than 5. Since 5 is not greater than 5, the answer to the question is NO. We've told Excel to display a message of "Less than 5", if the answer was NO. In other words, we didn't tell Excel what to do if the value in cell A1 was the same as 5.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The solution to this is to use a different Conditional Operator. We used the Greater Than ( > ) operator. Here's some more:</div>
<span class="TextIndent3" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif; margin-left: 5em;"><      Less Than</span><span style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"></span><br />
<div class="TextIndent3" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif; margin-left: 5em;">
>=    Greater than Or Equal To</div>
<div class="TextIndent3" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif; margin-left: 5em;">
<=    Less than Or Equal To</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<span class="TextIndent3" style="margin-left: 5em;"><>    Not Equal To</span></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
For the second and third operators above, you type an angle bracket followed by the equals sign. There are no spaces between the two. For the final one, it's a left angle bracket followed by a right angle bracket.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
So for our exercise, the symbol we should have used was the one for Greater than Or Equal To. Change your IF function to this and try again:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=IF(A1 >= 5, "Greater than or Equal to Five", "Less than Five")</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /><b>Exercise</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Test the A1 cell to see if the value is less than or equal to 5. If it is, display a suitable message. If it's not, display the message "Greater than Five".</div>
<h3 align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Complex If Functions</h3>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The If Functions you've just met are consider fairly simple ones. They can get really complex!</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Consider our Student Exam problem. The spreadsheet we created to track our students looks like this, from an earlier section:</div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="162" src="http://www.homeandlearn.co.uk/excel2007/images/ReviewGrades2.gif" width="620" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
However, we want to display the following grades as well:</div>
<div class="TextIndent3" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif; margin-left: 5em;">
<b>A</b> If the student scores 80 or above<br /><b>B</b> If the student scores 60 to 79<br /><b>C</b> If the student scores 45 to 59<br /><b>D</b> If the student scores 30 to 44<br /><b>FAIL</b> If the student scores below 30</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
With such a lot to check for, what will the IF Function look like? Here's one that works:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=IF<span style="color: red;">(</span>B2>=80, "A", IF<span style="color: blue;">(</span>B2>=60, "B", IF<span style="color: #006600;">(</span>B2>=45, "C", IF<span style="color: #990000;">(</span>B2 >=30, "D", "Fail" <span style="color: #990000;">)</span> <span style="color: #006600;">)</span> <span style="color: blue;">)</span> <span style="color: red;">)</span></b></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Quite long, isn't it? Look at the colours of the round brackets above, and see if you can match them up. What we're doing here is adding more IF Functions if the answer to the first question is NO. If it's YES, it will just display an "A".</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
But take a look at our Student Exam spreadsheet now:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="331" src="http://www.homeandlearn.co.uk/excel2007/images/2IfStatements2.gif" width="620" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
After the correct answer is displayed in cell B14 on the spreadsheet above, we used AutoFill for the rest!</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Don't worry if that long IF statement is making your brain hurt - it is quite complicated.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the next part, we'll take a look at Conditonal Formatting. This is about colouring cells depending on their values. Looks nice on a spreadsheet!</div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
</div>

Microsoft Excel 2007 to 2013 - The IF Function

The IF function can be quite useful in a spreadsheet. It is used when you want to test for more than one value. For example, has a bill be...