MULTI IN ONE: Microsoft Excel 2007 to 2013

Microsoft Excel 2007 to 2013 - SumIF

<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Another useful Excel function is SumIF. This function is like CountIf, except it adds one more argument:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>SUMIF(<span style="color: red;">range</span>, <span style="color: blue;">criteria</span>, <span style="color: #006600;">sum_range</span>)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Range and criteria are the same as with CountIF - the range of cells to search, and what you want Excel to look for. The Sum_Range is like range, but it searches a new range of cells. To clarify all that, here's what we'll use SumIF for. (Start a new spreadsheet for this.)</div>
<div align="center" 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;">
Five people have ordered goods from us. Some have paid us, but some haven't. The five people are Elisa, Kelly, Steven, Euan, and Holly. We'll use SumIF to calculate how much in total has been paid to us, and how much is still owed.</div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
So in Column A, enter the names:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Create the following Excel 2007 Spreadsheet" height="181" src="http://www.homeandlearn.co.uk/excel2007/images/2SumIF.gif" width="332" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In Column B enter how much each person owes:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img height="179" src="http://www.homeandlearn.co.uk/excel2007/images/2SumIF2.gif" width="428" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In Column C, enter TRUE or FALSE values. TRUE means they have paid up, and FALSE means they haven't:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img height="180" src="http://www.homeandlearn.co.uk/excel2007/images/2SumIF3.gif" width="473" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Add two more labels: Total Paid, and Still Owed. Your spreadsheet should look something like this one:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="257" src="http://www.homeandlearn.co.uk/excel2007/images/2SumIF4.gif" width="473" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In cells B10 and B11, we'll use a SumIF function to work out how much has been paid in, and how much is still owed. Here's the SumIF function again:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>SUMIF(<span style="color: red;">range</span>, <span style="color: blue;">criteria</span>, <span style="color: #006600;">sum_range</span>)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
So the range of cells that we want to check are the True and False values in the C column; the criteria is whether they have paid (True); and the Sum_Range is what we want to add up (in the B column).</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In cell B10, then, enter the following formula:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=SUMIF(<span style="color: red;">C3:C7</span>, <span style="color: blue;">TRUE</span>, <span style="color: #006600;">B3:B7</span>)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
When you press the enter key, Excel should give you the answer:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="284" src="http://www.homeandlearn.co.uk/excel2007/images/2SumIF5.gif" width="473" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
So 265 is has been paid in. But we told SumIF to first check the values in the cells C3 to C7 (range). Then we said look for a value of TRUE (criteria). Finally, we wanted the values in the B column adding up, if a criteria of TRUE was indeed found (sum_range).</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;">
Use SumIF to work out how much is still owed. Put your answer in cell B11.</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;">
We'll leave the subject of Conditional Logic. In the next section, more things you can do with Excel!</div>
</div>

Microsoft Excel 2007 to 2013 - SumIF

Another useful Excel function is SumIF. This function is like CountIf, except it adds one more argument: SUMIF( range , criteria , sum...

Microsoft Excel 2007 to 2013 - CountIF in Excel

<div dir="ltr" style="text-align: left;" trbidi="on">
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Another useful function that uses Conditional Logic is CountIF. This one is fairly straightforward. As its name suggests, it counts things! But it counts things IF a condition is met. For example, keep a count of how many students have an A Grade.</div>
<div align="center" 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;">
To get you started with this function, we'll use our Student Grade spreadsheet and count how many students have a score of 70 or above. First, add the following label to your spreadsheet:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="A new label has been added to the K column" border="1" height="205" src="http://www.homeandlearn.co.uk/excel2007/images/2CountIF.gif" width="428" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
As you can see, we've put our new label at the start of the K column.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
We can now use the CountIF function to see how many of the students scored 70 or above for a given subject.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The CountIF function looks like this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>COUNTIF(</b><span style="color: red;">range</span>, <span style="color: blue;">criteria</span><b>)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The function takes two arguments (the words in the round brackets). The first argument is range, and this means the range of cells you want Excel to count. Criteria means, "What do you want Excel to look for when it's counting?".</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
So click inside cell K2, and then click inside the formula bar at the top. Enter the following formula:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=CountIf(</b><span style="color: red;">B2:I2</span><b>, "</b><span style="color: blue;">>= 70</span><b>")</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The cells B2 to I2 contain the Math scores for all 8 students. It's these scores we want to count.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Press the enter key on your keyboard. Excel should give you an answer of 4:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="A CountIF Function in Excel 2007" border="1" height="110" src="http://www.homeandlearn.co.uk/excel2007/images/2CountIF2.gif" width="469" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
(If you're wondering where the columns B to I have gone in the image above, we've hidden then for convenience sake!)</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
To do the rest of the scores, you can use AutoFill. You should then have a K column that looks like this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="186" src="http://www.homeandlearn.co.uk/excel2007/images/2CountIF3.gif" width="469" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
By using CountIF, we can see at a glance which subjects students are doing well in, and which subjects they are struggling in.</div>
<div align="center" 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;">
<b>Exercise</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Add a new label to the L column. In the cells L2 to L9, work out how many students got below 50 for a given subject. You should get the same results as in the image below:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="187" src="http://www.homeandlearn.co.uk/excel2007/images/2CountIF4.gif" width="582" /></div>
<div align="left" 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;">
In the next part, we'll look at a function similar to CountIF - SumIF.</div>
</div>

Microsoft Excel 2007 to 2013 - CountIF in Excel

Another useful function that uses Conditional Logic is CountIF. This one is fairly straightforward. As its name suggests, it counts things...

Microsoft Excel 2007 to 2013 - Conditional Formatting

<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
You can use something called Conditional Formatting in your Excel spreadsheets. Conditional Formatting allows you to change the appearance of a cell, depending on certain conditions. What we'll do is to colour the Overall Averages on our Student Exam spreadsheet, depending on the grade. Here's the spreadsheet we'll be working on.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The Student Grades Spreadsheet" border="1" height="206" src="http://www.homeandlearn.co.uk/excel2007/images/ReviewGrades3.gif" width="666" /></div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Open up <a href="http://www.homeandlearn.co.uk/excel2007/excel2007s5p6.html">your Student Exam spreadsheet</a> (You did complete it, didn't you?)</li>
<li>Highlight the cells with Overall Grades, which should be cells B11 to I11</li>
</ul>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The Overall Averages range from 44 to 85. We'll colour each grade, depending on a scale. A different colour will apply to the following grades:</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>50 and below</li>
<li>51 to 60</li>
<li>61 to 70</li>
<li>71 to 80</li>
<li>81 and above</li>
</ul>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
So five different bands, and a colour for each. To set the Conditional Formatting in Excel, do the following:</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>With your Overall Averages highlighted, click on the Home menu at the top of Excel</li>
<li>Locate the <b>Styles</b> panel, and the <b>Conditional Formatting</b> item:</li>
</ul>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The Styles Panel in Excel 2007" height="85" src="http://www.homeandlearn.co.uk/excel2007/images/2StylesPanelConForm.gif" width="172" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The Conditional Formatting menu gives you various options. The easiest one is the Colour Scales option. Select one of these and Excel will colour the cell backgrounds for you:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Color Scales in Excel 2007" height="294" src="http://www.homeandlearn.co.uk/excel2007/images/2StylesPanelConForm2.gif" width="340" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
That's not quite what we're looking for, though. We'd like to choose our own values. So click on<b>More Rules</b>, from the <b>Colour Scales</b> submenu. You'll see the following rather complex dialogue box:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="New Formatting Rule dialogue box" height="432" src="http://www.homeandlearn.co.uk/excel2007/images/2ConFormattingDB.gif" width="406" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The one we want is the second option, <b>Format only cells that contain</b>. This will allow us to set up our values. When you click this option, the dialogue box changes to this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img height="384" src="http://www.homeandlearn.co.uk/excel2007/images/2ConFormattingDB2.gif" width="555" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The part we're interested in is the bottom part, under the heading <b>Edit the Rule Description</b>. It says <b>Cell Value</b> and <b>Between</b>, in the drop down boxes. These are the ones we want. We only need to type a value for the two boxes that are currently blank in the image above. We can then click the <b>Format</b> button to choose a colour.</div>
<div align="center" 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;">
So type 0 in the first box and 50 in the second one:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="151" src="http://www.homeandlearn.co.uk/excel2007/images/2ConFormattingDB3.gif" width="539" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Then click the <b>Format</b> button. You'll get another dialogue box popping up. This is just the Format Cells one though. You've met this before. Click on the Fill tab and choose a colour. Click OK and you should see something like this under Edit the Rule Description:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="148" src="http://www.homeandlearn.co.uk/excel2007/images/2ConFormattingDB4.gif" width="541" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The Preview is showing the colour we picked. So we've said, "<b>If</b> the Cell Value is between 0 and 50 <b>then</b> colour the cell Red".</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Click OK on this dialogue box to get back to Excel. You should find that one of the cells has turned red. To format the rest of the cells, click on Conditional Formatting on the Styles panel again. From the menu, click on <b>Manage Rules</b>:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img height="360" src="http://www.homeandlearn.co.uk/excel2007/images/2StylesPanelConForm3.gif" width="178" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
You'll get yet another complex dialogue box popping up! This one:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img height="300" src="http://www.homeandlearn.co.uk/excel2007/images/2ConFormatRulesDB.gif" width="549" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Our first rule is already there - Cell Value Between. The only thing we're doing here is adding New Rules, similar to the one we've just set up. Click the <b>New Rule</b> button then. You'll see the exact same dialogue boxes you used to set up the first rule. Set a new colour for the next scores - 51 to 60. Choose a colour, and keep clicking OK until you get back to the Rules Manager dialogue box. It should now look something like this one:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img height="300" src="http://www.homeandlearn.co.uk/excel2007/images/2ConFormatRulesDB2.gif" width="549" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
We now have to colours in our range. Do the rest of the scores, choosing a colour for each. The scores are these, remember:</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>50 and below</li>
<li>51 to 60</li>
<li>61 to 70</li>
<li>71 to 80</li>
<li>81 and above</li>
</ul>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
When you've done them all, your dialogue box should have five colours:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img height="300" src="http://www.homeandlearn.co.uk/excel2007/images/2ConFormatRulesDB3.gif" width="549" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The colours above are entirely arbitrary, and you don't have to select the same ones we did. The point is to have a different colour for each range of scores. But click OK when you're done. Your Overall Averages will then look something like this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="221" src="http://www.homeandlearn.co.uk/excel2007/images/2ConFormattingSpread.gif" width="664" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Formatting your spreadsheet in this way allows you to see at a glance relevant information. In the spreadsheet above, it's obvious who's failing - just look for the red cells!</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 look at a useful function that counts things - COUNT IF.</div>
<div>
<br /></div>
</div>

Microsoft Excel 2007 to 2013 - Conditional Formatting

You can use something called Conditional Formatting in your Excel spreadsheets. Conditional Formatting allows you to change the appearance...

Microsoft Excel 2007 to 2013 - 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...