Finding and Managing Formula Errors in Excel

Are you wondering why Excel is returning an Error instead of a number or maybe sometimes not the expected return value?

Many times while we create a formula we get into a situation where the formula returns errors like division error (#DIV/0!), value not available (#N/A), cell reference is not valid (#REF!) including other errors in Excel.

In this tutorial, we will see how to resolve, check, and manage errors, additionally, you will also find two bonus questions that will help to understand errors.

Why Excel returning Zero or Error for the formula

Let’s start with an example. In this case, we have two functions SUMIF and COUNTIF, in a formula. The primary purpose of this formula is to calculate the average quantity of each Gadget.

But in this case, it shows an error, or you can say it is returning a Zero value. But this is not expected from the formula which we have built based on our understanding. So let’s try to find out ways given by Excel to find any error in the given Excel formula. Let’s first start to evaluate this Excel formula.

shows an error, or you can say it is returning a Zero value. But this is not expected from the formula

Audit formula errors

The evaluate formula show the result of the underlying expression. If you click evaluate, you can see there is a change in the formula box here. It is trying to show the values step by step within the formula.

The first part of the formula, which is related to SUMIF, returns zero, well you can see here there is the quantity, and we are looking to find an average. So it should have some value here.

And now see the COUNTIF section, so it is three, so the number of mobile counts is three, which is right. So the problem lies in the first part of the formula related to the SUMIF function.

Breaking down large formula with the F9 shortcut key

Now, let’s check another way to finding erros in a formula, or you can see auditing a formula to find an error. This is using keyboard shortcut F9.

The shortcut is one of the handiest tools you will be using when working on Excel, and you are looking to find where the error is. You select the function here and click F9. You can see the number of the return value of this particular part of the formula.

Likewise, you can do this for the first part of the formula, which is zero again, which is the same as Evaluate, which you can see is an error; don’t forget to click escape after F9

We can also get into each of the arguments of a given function and click F9. Here, we can see how handy this keyboard shortcut can be for evaluating a formula; in this case, we can see that the range and the sum range are both the same, which may be a problem.

Deep-dive an Excel Formula with Insert Function

We can also look into the Insert Function using this popup box, and it gets easy to identify any error. Here you can see the explanation of each argument. So, in this case, it is a range of cells you want to evaluate.

In this case, we have selected the wrong range. We want to assess the Gadget column, not the Purchase, to go here and change the selected range. You can also see the results in the function argument box; click Ok. You can see the final quantity or say the average amount using this formula is returning the right value.

So in this example, we understand using the evaluate formula, F9 shortcut key, and how to use the Insert function, all these three Excel tools can be beneficial in finding errors.

Error from external link and reference

Next, we will look into applying Trace Precedents and Trace Dependents to find an Excel error. Trace Precedent and Trace Dependents are a convenient tool for Excel to check the accuracy or mistake of a complex formula, especially when linked to multiple cells on a different worksheet or workbook.

Find a cell or group of cells that affect or dependent - Trace Links

Trace Precedents is to find the cell or group of cells that affect the active cell’s value.

Like in this case, if you click Trace precedents, you will see these two cells, and this group of cells is affecting this value. The blue arrow is the cell that is showing no error, and the red arrow is from where the error is flowing through this cell. To remove trace arrows, you can click remove arrows here.

The Trace Dependents is to know which cell this set is affecting.  So if we click trace dependents, we can see that this cell is affected by this cell. So C16 is the dependent cell of C15.

Finding errors by tracing links of a formula

Now let’s use Trace Precedents and Trace Dependents to find the error in this table. Here we are doing some forecasting based on the input worksheet. And we are getting some error.

So if you click Trace precedents here, you can see that the error comes from this particular cell. If you again click the Trace precedents, you will see the next layer of the link coming from this cell. And this is also red.

If we click again, Trace precedents, you will see that there is a black arrow. It indicates that the value of C7 is coming from another worksheet. This icon is a worksheet icon. If we go and click on this, the black arrow, you can see the link and click Ok. You can see that the cell, which we see in C7, is linked to this particular cell (H7).

Similarly, we can use trace precedents here, as well, to understand the error. If you click here Trace precedents, we can see that the selection.

In this case, both arrows are blue, and Excel does not recognize the error here; otherwise, the arrow would have been red. But if we look inside the insert function closely, we can see that the final result shows an error.

In the SUMPRODUCT function, the range select for array one and array two must be similar. Like in this case, the rent per month total range selected is one more than the number of units. So we can go and change this selection.

Let’s do this. We can now see that the final value is right, and we can see all the errors are gone.

Why showing formula instead of result?

Now let’s try to see some of the critical mistakes a user can make while writing a formula.

Many times I have created a formula, I found there are errors. Let’s check this example; in cell E6, we can see a division formula of D6 and B6, but we don’t see any value or any error. It is merely showing us cell links.

The reason behind this is that of the format of this particular cell. It is essential to make sure that the formula’s cell format should not be ‘Text.‘ It should always be number currency or percentage or accounting, currency, but not Text.

In this case, I would change it to percentage format. To reflect the change, select the cell and click F2 and Enter now, we can see an error, not just a text. Similarly, we can do it for other cells as well.

Why Green Triangle in cell? How to fix and remove?

But in this case, Excel is returning the number, not error, because we have a denominator value. If you will see this error, there is a green triangle, and over here, you can see a notification #DIV ‘Divided by Zero error.’

There is a zero in the denominator that is resulting in a divide by zero error. You can look into error checking options here. Look, if you will wish to change the color green to something else. It is up to you; there is a different kind of error checking rules as well, you can tick and untick, and that’s how this small triangle will disappear.

Replace and change #DIV, #NA #Value, #Ref Errors

But in this case, we want to see how to manage this error. As we can see, there is a ‘divide by zero error.’ To handle these errors, you must be sure that this formula is right; now, the second step is to manage this particular error value.

In this case, we will use an essential function, the IFERROR function. IFERROR returns a different value in case the return value is an error. In this case, I would make it a Text. Now in the place of error, Excel is returning not available, and the green triangle disappeared. Similarly, you can do for others just by dragging down so we can see all this where there was an error is now not #NA.

And just by simply using the IFERROR function. So in this example, you understand to make sure a formula should not have a text format. And if there is a return value, which is an error, you should use the IFERROR function.

Two Bonus Questions

Let’s check out our next section of the tutorial. Bonus questions are these two questions, which will handle two different kinds of errors. It will give you an idea of when you will be using an Excel formula, find an error, and manage them. So let’s check out both questions.

Please download the Excel workbook and figure out both questions to show you the answer.

Avoid Errors in the Range to calculate (Average, Add & Count)

Question: We have an error, I have input SUM function here and select the range, but it is returning an error so think and find the best way to manage these errors.

Answer: In the first problem, we were getting errors when we were using only the SUM function. To manage such kind of problem or error, you can use SUMIF and AGGREGATE functions. In the SUMIF function, we can condition that if there is a valve, which is #NA error in this example, it should be excluded while adding this range. So we will exclude #NA error in the range using not equal to sign.

Similarly, if you want to average or count a range of data with an error, we can use AVERAGEIF and COUNTIF Excel function, with not equal to a sign. And we can also use the AGGREGATE function. The AGGREGATE  function is very robust. We can use this function in multiple ways; if we go and check out the options, it gives an idea of what kind of function you want to apply on a given range. In this case, I have selected nine that apply SUM. You can also select average or count based on your requirement to manage errors.

And then in the option, you can select a different kind of possibilities again. Here I have selected “Ignore the error values”. You can choose based on your requirements, and then we will opt for the array or the range.

Logically True (Equal) but not returning the right value (error)

Question 2: Now, let’s check out our second question; in this case, I have used the INDEX and MATCH function to create this formula in which if I enter a number, here in cell F18 the formula, return the value from the column B. But suppose I go and manually change the number here, as you can see. In that case, at 0.419 in the formula bar, the INDEX and MATCH function is not returning any value from column B. However, if you see actually, both of them are logically true, but when I go and copy this number and directly paste it here.

You can then see the Excel is returning the value from column B and the right number, whereas if you will see the formula bar the number is precisely the same. There is no difference. So when I enter the same number manually, Excel returns an error, whereas when I paste the value directly in the cell, Excel returns the right value. So what is going on? Just try to think about it.

Answer 2:

Now, let’s check out our second example. In this case, we were getting errors when we enter the value manually. Whereas when we copy-paste the value from the cell directly, Excel returns the right value. The primary reason for such kind of error is the “Binary floating-point.” This is the anomaly in Excel.

Sometimes Excel compares data by rounding a digit to 15 significant digits maximum. Whereas sometimes, it returns a value based on the exact binary difference that means without rounding a value. See this cell; in this case, I have multiplied two large numbers and Excel is returning an exponential (E) error because the return value is more than 15 digits. Sometimes, Excel compares two digits after rounding it to 15 digits, and sometimes Excel doesn’t do the rounding. It checks the exact binary difference.

So in the case of INDEX and MATCH, it checks the exact binary difference. And in the case of a logical check, it checks 15 significant digits difference. And that’s why even though logically both numbers were correct, INDEX & MATCH returns an error. So, in this case, we can use the ROUND function. Now you can see that the number I pasted was precisely 0.419, but now Excel is returning an error after rounding. If I enter manually now, Excel will return the correct value.

In case of the DATE is matching with the equal sign (Logically True) but the result is not showing or errors then use within the INT Excel function.

So the reason for this error was the binary difference and how Excel compares the same number based on different conditions.