IF Statements

Download

HTML can be useful for transferring content to other online publishing platforms, typically by copy/pasting to the new platform.

In this chapter, we’re going to look at conditional logic in Excel—specifically IF statements. We’ll start with a spreadsheet that lists employees for a company and includes their gender, position, age, college GPA, and current salary.
<div class="callout default" data-callout-custom-class="default">
<h3 id="h3_AHqq" name="h3_AHqq">Practice Spreadsheet</h3>
Use this <a href="https://drive.google.com/uc?export=download&id=1DWqacPiAX9yvFAzxWIADT1qRtriXmMqH" data-shortened-url="https://books.byui.edu/-ofBz" data-unshortened-url="https://drive.google.com/uc?export=download&id=1DWqacPiAX9yvFAzxWIADT1qRtriXmMqH">workbook</a> for the chapter.
</div>
<figure class="image text-center"><img class="none" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_17.1.png" alt="BUS115_image_17.1.png" width="740" height="648" id="img_bXYW" name="img_bXYW">
<figcaption class="text-center">Figure 17.1</figcaption>
</figure>
<h2 id="h2_npho" name="h2_npho">Dashboard Setup</h2>
Next, we’ll create a dashboard. The dashboard will include input and output cells. The input cells labeled as “Conditional Inputs” will include gender, age, retirement age, years window, bonus percent, raise over percent, raise under percent, and two GPA cells. The output cells labeled as “Conditional Outputs” will include average salary, number of employees, and employees eligible to retire. Finally, we’ll insert data into the input cells. (See Figure 17.1)
<figure class="image text-center"><img class="none" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_17.2.png" alt="BUS115_image_17.2.png" width="801" height="438" id="img_QMmw" name="img_QMmw">
<figcaption class="text-center">Figure 17.2</figcaption>
</figure>
<div class="clearfix"> </div>
<ul id="ul_dhbh" name="ul_dhbh">
<li id="li_qJXg" aria-level="1" name="li_qJXg">Gender will contain a data list with Male and Female options created with the Data Validation tool.</li>
<li id="li_HgHJ" aria-level="1" name="li_HgHJ">Age is 40.</li>
<li id="li_wxLW" aria-level="1" name="li_wxLW">Retirement age is 65.</li>
<li id="li_FufK" aria-level="1" name="li_FufK">Years window is 35.</li>
<li id="li_oxpJ" aria-level="1" name="li_oxpJ">GPA related to bonus is 3.5.</li>
<li id="li_YPBa" aria-level="1" name="li_YPBa">Bonus percent is 10%.</li>
<li id="li_WVkX" aria-level="1" name="li_WVkX">GPA related to raise is 3.0.</li>
<li id="li_FRjW" aria-level="1" name="li_FRjW">Raise percent over is 8%.</li>
<li id="li_CjPe" aria-level="1" name="li_CjPe">Raise percent under is 3%.</li>
</ul>
Note: We can name these cells as we learned in the “Named Ranges” chapter. The cell names can be used hereafter in the formulas we will work on within this chapter.
<h2 id="h2_siFU" name="h2_siFU">IF Data and Average</h2>
On the data spreadsheet shown in Figure 17.1, if we want to find the average salary of employees of a specific gender, we can use an IF function to list salaries in an empty column based on the dashboard’s input cell for employees’ gender. The IF function will check if an operation (equal to, less than, or greater than) involving two data values is true or false, and it can display a specific result if true or a different result if false. In this example, the logic test will check if the employee’s gender matches the gender displayed on the dashboard, and if true, it will display the employee’s salary. Then, we can reference the column’s cells in an AVERAGE function to find the total average salary for that gender.
<ol id="ol_rJXr" name="ol_rJXr">
<li id="li_Cyrd" aria-level="1" name="li_Cyrd">Type =IF and a left parenthesis ( ( ) in an empty cell.</li>
<li id="li_ADHH" aria-level="1" name="li_ADHH">Select the employee’s gender cell for the logic test’s first value (C3 in Figure 17.3).</li>
<li id="li_dcNq" aria-level="1" name="li_dcNq">Type an equals sign ( = ).</li>
<ol id="ol_IgeL" style="list-style-type: lower-alpha;" name="ol_IgeL">
<li id="li_CiZs" aria-level="2" name="li_CiZs">In other situations, a less than symbol ( < ) or greater than symbol ( > ) may also be used to test values in an IF logic test.</li>
</ol>
<li id="li_amMp" aria-level="1" name="li_amMp">Select the dashboard’s gender input cell or type the cell’s name for the logic test’s second value (the dashboard’s gender input cell in Figure 17.3).</li>
<ol id="ol_FHhe" style="list-style-type: lower-alpha;" name="ol_FHhe">
<li id="li_opcA" aria-level="2" name="li_opcA">The cell being compared in Figure 17.3 is from the dashboard sheet and has been named “in_gender.” If you named the cell differently, type the correct name. Otherwise, select the appropriate cell from the dashboard sheet.</li>
</ol>
<li id="li_bGdI" aria-level="1" name="li_bGdI">Type a comma ( , ) and select the appropriate salary cell to display its value if the logic test is true (G3 in Figure 17.3).</li>
<li id="li_vReh" aria-level="1" name="li_vReh">Type a comma ( , ) and two double quotation marks ( " ).</li>
<ol id="ol_fify" style="list-style-type: lower-alpha;" name="ol_fify">
<li id="li_rWdX" aria-level="2" name="li_rWdX">The double quotation marks signify a text string. However, since no text is inserted between the marks, the cell will not display anything if the logic test is false.
<figure class="image text-center"><img class="centered col-12" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_17.3.png" alt="BUS115_image_17.3.png" id="img_fvwu" name="img_fvwu">
<figcaption class="text-center">Figure 17.3</figcaption>
</figure>
<div class="clearfix"> </div>
</li>
</ol>
<li id="li_xAFv" aria-level="1" name="li_xAFv">Type a right parenthesis ( ) ) and press Enter to complete the formula (=IF(C3=in_gender,G3,"")).</li>
<ol id="ol_mIUt" style="list-style-type: lower-alpha;" name="ol_mIUt">
<li id="li_PwFY" aria-level="2" name="li_PwFY">Notice the formula in Figure 17.3 displays additional text for the referenced cells. Excel requires the appropriate spreadsheet’s name to be included in the formula when referencing cells on another spreadsheet. In this case, the first referenced cell is 'IF Statements'!C3 (including the single quotation marks enclosing the spreadsheet name and followed by an exclamation mark).</li>
</ol>
</ol>
Copy the formula down the column for each employee. Finally, create an AVERAGE function on the dashboard's Average salary output cell to reference this column's cells. The cell will display an overall average based on the gender selected in the dashboard's Gender input cell.
Note: There is a specific function known as AverageIFS that can calculate the same result using similar formula concepts in a single cell. Please research the AverageIFS function and learn more about how it can calculate the same result.
<h2 id="h2_uLst" name="h2_uLst">IF Data and Sum</h2>
If we want to find the total employees under a specific age, we can use an IF function in an empty column to check if the employee’s age is less than the age specified on the dashboard’s Age input cell.
<ol id="ol_pVEN" name="ol_pVEN">
<li id="li_NHwH" aria-level="1" name="li_NHwH">Type =IF and a left parenthesis ( ( ) in an empty cell.</li>
<li id="li_xuAK" aria-level="1" name="li_xuAK">Select the employee’s age cell for the logic test’s first value (E3 in Figure 17.4).
<figure class="image text-center"><img class="centered col-12" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_17.4.png" alt="BUS115_image_17.4.png" id="img_oxNC" name="img_oxNC">
<figcaption class="text-center">Figure 17.4</figcaption>
</figure>
<div class="clearfix"> </div>
</li>
<li id="li_cKqt" aria-level="1" name="li_cKqt">Type a less than sign ( < ).</li>
<li id="li_ygQv" aria-level="1" name="li_ygQv">Select the dashboard’s age input cell or type the cell’s name for the logic test’s second value.</li>
<ol id="ol_xpWX" style="list-style-type: lower-alpha;" name="ol_xpWX">
<li id="li_fiUP" aria-level="2" name="li_fiUP">In Figure 17.4’s example, the referenced cell’s name is in_age.</li>
</ol>
<li id="li_KLQZ" aria-level="1" name="li_KLQZ">Type a comma ( , ) and a 1.</li>
<li id="li_upaY" aria-level="1" name="li_upaY">Type a comma ( , ) and a 0.</li>
<li id="li_BXcr" aria-level="1" name="li_BXcr">Type a right parenthesis ( ) ) and press Enter to complete the formula (=IF(E3<in_age,1,0)).</li>
</ol>
Copy the formula down the column for each employee. Finally, create a SUM function on the dashboard's # of employees under the output cell to reference this column's cells. The cell will display a total of employees under the age specified in the Age input cell.
<h2 id="h2_PoMt" name="h2_PoMt">IF Data and Operations</h2>
If we want to determine an employee’s raise based on their college GPA, we can write a formula to check the GPA and whether the raise will be 3% or 8%.
<ol id="ol_URsN" name="ol_URsN">
<li id="li_UpMK" aria-level="1" name="li_UpMK">Type =IF and a left parenthesis ( ( ) in an empty cell.</li>
<li id="li_simX" aria-level="1" name="li_simX">Select the cell for the employee’s GPA (F3 in Figure 17.5).</li>
<li id="li_utxI" aria-level="1" name="li_utxI">Type a greater than sign ( > ).</li>
<li id="li_sczS" aria-level="1" name="li_sczS">Select the cell for GPA raise on the dashboard sheet.</li>
<ol id="ol_HcZr" style="list-style-type: lower-alpha;" name="ol_HcZr">
<li id="li_aFmy" aria-level="2" name="li_aFmy">In Figure 17.5, the referenced cell’s name is in_raise_gpa. If you named the cell differently, type the correct name. Otherwise, select the appropriate cell from the dashboard sheet.</li>
</ol>
<li id="li_tRvm" aria-level="1" name="li_tRvm">Type a comma ( , ) and select the cell for raise percent over to indicate the raise percent for a GPA over the specified value.</li>
<ol id="ol_jLjA" style="list-style-type: lower-alpha;" name="ol_jLjA">
<li id="li_eqwE" aria-level="2" name="li_eqwE">In Figure 17.5, the referenced cell’s name is in_raise_over.</li>
</ol>
<li id="li_oxVW" aria-level="1" name="li_oxVW">Type a comma ( , ) and select the cell for raise percent under to indicate the raise percent for a GPA under the specified value.</li>
<ol id="ol_GpWe" style="list-style-type: lower-alpha;" name="ol_GpWe">
<li id="li_fLXu" aria-level="2" name="li_fLXu">In Figure 17.5, the referenced cell’s name is in_raise_under.</li>
</ol>
<li id="li_VNbV" aria-level="1" name="li_VNbV">Type a right parenthesis ( ) ) to complete the formula (=IF(F3>in_raise_gpa,in_raise_over,in_raise_under)).</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_17.5.png" alt="BUS115_image_17.5.png" id="img_oGpx" name="img_oGpx">
<figcaption class="text-center">Figure 17.5</figcaption>
</figure>
<div class="clearfix"> </div>
Format the cell as a percentage and copy the formula down the column for each employee.
If we want to find the raise dollar amount, we can multiply the percentage by the current salary in a new cell. Alternatively, we can copy or add to the raise percentage formula to multiply by the salary (=IF(F3>in_raise_gpa,in_raise_over,in_raise_under)*G3).
If we want to know the total new salary, we can modify the formula to add the IF calculation to 1 which signifies 103% or 108% (depending on the percent returned based on the employee’s GPA). The formula will calculate the total new salary in a single cell.
<ol id="ol_vVYt" name="ol_vVYt">
<li id="li_gupd" aria-level="1" name="li_gupd">Type an equals symbol ( = ) and a left parenthesis ( ( ) in a new cell.</li>
<li id="li_oKGw" aria-level="1" name="li_oKGw">Type 1 to represent 100% and an addition sign ( + ).</li>
<li id="li_ubMg" aria-level="1" name="li_ubMg">Type IF and a second left parenthesis ( ( ).</li>
<li id="li_KfEW" aria-level="1" name="li_KfEW">Select the cell for the employee’s GPA (F3 in Figure 17.6).</li>
<li id="li_sIYG" aria-level="1" name="li_sIYG">Type a greater than sign ( > ).</li>
<li id="li_uJKF" aria-level="1" name="li_uJKF">Select the cell for GPA raise on the dashboard sheet.</li>
<ol id="ol_Udvb" style="list-style-type: lower-alpha;" name="ol_Udvb">
<li id="li_kZCt" aria-level="2" name="li_kZCt">In Figure 17.6, the referenced cell’s name is in_raise_gpa. If you named the cell differently, type the correct name. Otherwise, select the appropriate cell from the dashboard sheet.</li>
</ol>
<li id="li_dPrV" aria-level="1" name="li_dPrV">Type a comma ( , ) and select the cell for raise percent over to indicate the raise percent for a GPA over the specified value.</li>
<ol id="ol_BsCr" style="list-style-type: lower-alpha;" name="ol_BsCr">
<li id="li_uwjm" aria-level="2" name="li_uwjm">In Figure 17.6, the referenced cell’s name is in_raise_over.</li>
</ol>
<li id="li_VNiT" aria-level="1" name="li_VNiT">Type a comma ( , ) and select the cell for raise percent under to indicate the raise percent for a GPA under the specified value.</li>
<ol id="ol_QtFe" style="list-style-type: lower-alpha;" name="ol_QtFe">
<li id="li_fZuh" aria-level="2" name="li_fZuh">In Figure 17.6, the referenced cell’s name is in_raise_under.</li>
</ol>
<li id="li_PnJp" aria-level="1" name="li_PnJp">Type a right parenthesis ( ) ) to close the IF logic formula.</li>
<li id="li_vEtx" aria-level="1" name="li_vEtx">Type a second right parenthesis ( ) ) to close the addition operation.</li>
<li id="li_Edub" aria-level="1" name="li_Edub">Type an asterisk ( * ) for multiplication.</li>
<li id="li_dFVo" aria-level="1" name="li_dFVo">Select the cell with the employee’s current salary (G3 in Figure 17.6).</li>
<li id="li_teWS" aria-level="1" name="li_teWS">Press Enter to complete the formula (=(1+IF(F3>in_raise_gpa,in_raise_over,in_raise_under))*G3).</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_17.6.png" alt="BUS115_image_17.6.png" id="img_wPJZ" name="img_wPJZ">
<figcaption class="text-center">Figure 17.6</figcaption>
</figure>
<div class="clearfix"> </div>
<div class="callout default" data-callout-custom-class="default">
<h3 id="h3_cFsb" name="h3_cFsb">Supplemental Resource</h3>
<iframe id="kaltura_player_1636661561" src="https://cdnapisec.kaltura.com/p/1157612/sp/115761200/embedIframeJs/uiconf_id/33020032/partner_id/1157612?iframeembed=true&playerId=kaltura_player_1636661561&entry_id=1_ozqazs4q" width="480" height="270" frameborder="0" allowfullscreen="allowfullscreen"></iframe>
</div>