Formula Errors

Download

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

<p id="p_ExLh" name="p_ExLh">In this chapter, we’re going to take a look at some of the common errors that occur when formulas are entered into Excel incorrectly.  </p>
<div class="callout default" data-callout-custom-class="default">
<h3 id="h3_Umws" name="h3_Umws">Practice Spreadsheet</h3>
<p id="p_KyJb" name="p_KyJb">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.</p>
<p id="p_qgYs" name="p_qgYs"><span style="font-size: 10pt;"><em>Note: This is the same workbook used in the If Statements chapter.</em></span></p>
</div>
<h2 id="h2_Mbyr" name="h2_Mbyr">Four Common Errors</h2>
<p id="p_utyd" name="p_utyd">A common problem that can produce errors is misspelling. For instance, let’s say we’d like to take the average of all of the ages. If you complete the formula but “average” is spelled incorrectly, Excel cannot recognize the formula itself. It will give you an error that says “<strong>#NAME?</strong>” This can be corrected by typing in the correct spelling of the formula and hitting <strong>Enter</strong>.</p>
<p id="p_bhuv" name="p_bhuv">Another type of error is called the <em>value error</em>. Let’s say you are trying to calculate the sum of a few random cells, but rather than using the sum formula, you just select cells and put the plus sign between them. If you select a cell that is text rather than numbers, it will show you an error that says, “#<strong>VALUE!</strong>” This is because it cannot calculate text. However, if you use the =SUM formula and include a text cell, it will calculate the sum because it has the capability to add together only the numeral cells. In the case of a value error, simply get rid of the cell that contains text.</p>
<p id="p_hIWN" name="p_hIWN">A third error type is the<em> divide-by-zero error</em>. For instance, if you type, “=100/0,” it will give you an error that says, “<strong>#DIV/0!</strong>” Since Excel can’t calculate an infinite number, it gives this error. For example, this could occur with sales history. If you’re trying to calculate the difference in sales between four years, in year zero, you have no “last-year’s sales.” For example, in the following data, if you divide the new year by the old year and <span class="glossary-link" role="button" data-anchor-name="subtract_">Subtract</span> one, it will give you the percentage increase. (See <strong>Figure 18.1</strong>)</p>
<figure class="col-12 text-center"><img src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_18.1.png" class="img-fluid" alt="BUS115_image_18.1.png" id="img_ykTw" name="img_ykTw">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 18.1</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<p id="p_pvaQ" name="p_pvaQ">However, if you drag that formula up to the other years, when it gets to <em>1</em>, there is no former year to divide it by, and you get a divide-by-zero error. </p>
<p id="p_uMva" name="p_uMva">The fourth type of error that we’ll look at is called the <em>reference error</em>. Let’s suppose we reference two cells and add them together, and then delete one of the cells being referenced or the row that a reference cell is in by right-clicking and selecting “delete the cell” or “delete the row.” It will give you an error that looks like “<strong>#REF!</strong>” in place of the referenced cell. This is because that cell technically no longer exists. However, if you select a referenced cell and just press the <strong>Delete</strong> key, the formula still calculates. This is because the <strong>Delete</strong> key only gets rid of the text, not the cell itself, whereas if you right-click and choose delete, it would delete the cell itself, and the formula would no longer understand what it is supposed to be referencing.</p>
<h2 id="h2_RGax" name="h2_RGax">IFERROR Formula</h2>
<p id="p_sjFR" name="p_sjFR">As we think about other ways to handle these various errors that we’ll see in Excel, there’s a formula to be made aware of called “IFERROR.”</p>
<ol id="ol_ULYP" name="ol_ULYP">
<li id="li_JTFI" aria-level="1" name="li_JTFI">To get to it, type <strong>=IFERROR(</strong>.
<ol style="list-style-type: lower-alpha;" id="ol_cfIN" name="ol_cfIN">
<li id="li_ZQYI" aria-level="2" name="li_ZQYI">You’ll notice it asks for a value as the first argument, and the second argument is for if there’s an error. (See <strong>Figure 18.2</strong>)<br>
<figure class="col-12 text-center"><img src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_18.2.png" class="img-fluid" alt="BUS115_image_18.2.png" id="img_oeif" name="img_oeif">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 18.2</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
</li>
<li id="li_BeiH" name="li_BeiH">For example, if the formula was to calculate the sum of two salaries, let’s say we want to anticipate there could be some sort of error in the future. </li>
<li id="li_rnfk" aria-level="2" name="li_rnfk">Reference cells <strong>G13 </strong>and <strong>G17 </strong>to add them together for the first argument.</li>
<li id="li_PHZD" aria-level="2" name="li_PHZD">For the second argument, put in a text string saying, <strong>Error Found</strong>. (See <strong>Figure 18.3</strong>)<br>
<figure class="col-12 text-center"><img src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_18.3.png" class="img-fluid" alt="BUS115_image_18.3.png" id="img_AzDp" name="img_AzDp">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 18.3</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
</li>
<li id="li_SrJf" name="li_SrJf">Delete <strong>G17 </strong>by right-clicking and selecting <strong>Delete</strong>.</li>
<li id="li_eqse" aria-level="2" name="li_eqse">You’ll notice once you do this that the original sum formula gives a <strong>#REF! </strong>error, but the IFERROR formula lets you know in regular text that an error was found.
<ol style="list-style-type: lower-roman;" id="ol_vPJp" name="ol_vPJp">
<li id="li_RBdu" aria-level="3" name="li_RBdu">Of course, a formula doesn’t have to be text strings. It could be a separate formula. This could be useful if the first formula receives an error and you want to use a different calculation instead.</li>
</ol>
</li>
<li id="li_rkoV" name="li_rkoV">How the formula works is that it first tries to calculate everything in the first argument. If an error of any type is found in the formula, it then reverts the response to the second argument.</li>
</ol>
</li>
</ol>
<p id="p_Hhyx" name="p_Hhyx">There are other formulas that you can use to direct certain types of errors. For example, you can use <strong>ISERR</strong>, and it checks whether a value is an error other than <strong>#N/A</strong>.</p>
<p id="p_JfUu" name="p_JfUu">While this has been a basic <span class="glossary-link" role="button" data-anchor-name="overview_">overview </span>of the error types you may see in Excel, the key takeaway from this chapter is that you should pay attention to potential areas where errors could occur, and you use formulas like IFERROR to mitigate the effects those errors might have on future calculations throughout your worksheet.</p>
<p id="p_LVkK" name="p_LVkK">For a closing example, let’s say you use the SUM formula to calculate the sum of all the salaries and suppose an error of some type is found within the formula. That sum formula now cannot calculate what had been where the error is now. So, as you’re thinking about where to mitigate error possibilities, if you have a formula that’s looking up a salary from another location, you may want to figure out a way for it to handle if that lookup fails so that your sum of all the salaries does not also fail.</p>
<div class="callout default" data-callout-custom-class="default">
<h3 id="h3_XEPK" name="h3_XEPK">Supplemental Resource</h3>
<p id="p_wHtT" name="p_wHtT"><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_ukoqvd8u" width="480" height="270" frameborder="0" allowfullscreen="allowfullscreen"></iframe></p>
</div>