Excel Formula Errors

When dealing with formulas you will get error messages time to time. They may seem similar to you at first glance. But actually every formula error gives you valuable information about what is wrong with your formula.

Lets have a look of what are these error messages and what do they tell us, therefore what do we need to do to correct these errors.

####

When you see this error message in any cell, it means cell value is longer than the cell width. So you need to arrange how your value is displayed in that cell to get rid of this error. This can be done by:

  • Increasing cell width for the cell with error
  • Formatting your value to get a shorter display
  • Decreasing font size untill value fits in the cell

#VALUE!

When you see this error message, it means that you are using wrong type of data for your formula or selecting a range for a  function that operates with a single cell. For example if you put a range of cells into a EXACT() function you will get a #VALUE error. To correct this error you need to check your formula to see what went wrong. Select the cell with error than click on the “!” symbol that pops up to the left side. From here click Show Calculation Steps and you will see where the problem is.

#DIV/0!

This error message indicates that your formula is trying to divide something to zero. Simple as it is. So if there is a problem in the formula, you just need to correct it. Or your formula may be ok but you tend may tend to have zeros as values time to time. In this situation you either going to ignore these error messages or use an iferror function to manage them.

#NAME?

When you see this error message most likely there is a typing error in your formula. It may be a misspelled function name, text without bracelets, etc. So just check your formula to see what is the problematic part. If you can’t locate the problem at a glance, use Show calculation Steps approach mentioned above.

#N/A

This error message indicates that you are trying to use a non-existent value in your formula. It is most likely to happen with lookup formulas. When your formula is looking up for a value but can’t find it, it will result a #N/A error. You can get this error also by using non-existent values as conditions in formulas. How to get rid of them? You need to either add that value to your lookup range or just use an iferror kind of approach to get past it.

#REF!

This error message indicates that one of the values that is used in your formula is no longer existing. You will most likely get this error message right after you edit something in your workbook like pasting some formulas, deleting sheets,etc. Because the change you made deleted one of the values in your formula. You can use Show calculation Steps approach mentioned above to pinpoint which value you are missing, than take necessary corrective actions.

#NUM!

This error message is likely displayed when result of your formula is either too large or too small for Excel to represent. You may also see this error when you use a non-numeric argument in a function that is looking for a value. You may correct this error by using smaller numbers.

#NULL!

You get this error message when you were specifying multiple ranges in a formula and forgot to put “;” in between. So if your formula looks like =SUM(A1:A5 B1:B5) you will get #NULL error. If you correct it like =SUM(A1:A5;B1:B5) #NULL error will disappear.

There is an exception though. When you forget ; in your multi range formulas but your ranges are intersecting, you will not get an error. Instead excel will make the calculation “only for intersecting values. For example, =SUM(A1:A5 A3:A8) will result as “A3+A4+A5”.

Circular Reference

When circular reference occurs, it will not be presented as an error message. Instead excel will display a message (Circular Reference Warning). This means your formula is set up to use it’s own result to give result. Normally this would create an endless loop, but excel simply gives zero as result.

An example of this situation is inputting =A1+B1 formula into cell A1. In this case formula will need a value from A1 to sum with value of B1. Yet value of cell A1 is the result of this formula. This is a clear circular reference.

Note: Sometimes excel will detect inconsistencies in your formulas located in neighboring cells. It will mark them for you to notice and check. This notifications doesn’t necessarily point to errors. But they shouldn’t be discarded since they point to possible errors.

Handling Excel Formula Errors

Excel also gives you options to choose for how do you want errors to be handled in your workbook. To get to this screen navigate to file tab in ribbon. Select options. Then select Formulas. In the lower half of this screen you will find these options.

(File -> Options -> Formulas)

error_checking

You can enable/disable error checking as a whole or partially from this screen as you see fit.

You can also set how errors displayed on printed worksheets. For more information Displaying Errors and Comments.

You may also like...

9 Responses

  1. David says:

    Any fix for the currency calculation error I keep seeing?
    Here is an example:
    Set field A1 it 0 decimal place numeric and fields B1 and C1 to currency data type
    A1=63
    B1=71.45
    C1=ROUND(A1*B1,2)

    C1 yields $ 4501.20 as the result – but should be $ 7501.35

    • melih says:

      Hi,
      Are you sure about that? I made that calculation with both unformatted and formatted fields. Both times results as 4501.35. I also used calculator for the third time, it is still 4501.35.

      • David says:

        Did you set A1 as 0 decimal place numeric, and cells B1 and C1 to currency with 2 decimal places?
        That seems to be what causes the problem in combination thru the ROUND function.

        • melih says:

          Sure. Tried again just now. Still 4501.35 .

          • David says:

            MY BAD, you are correct, it has an intermediary calculation in B1, the 1.33 is what triggers the rounding to occur before the next sequence calculation in C1.
            A1=63
            B1=53.72*1.33
            C1=ROUND(A1*B1,2)

            B1 actually = 71.4476 not 71.45 and that’s where the value rounds.
            Oddly its rounding up in B1, but the end result in C1 is short.

          • David says:

            The 2 decimal place currency being multiplied by 1.33 is why this recurs.
            Its a parts markup amount so it appears frequently in my spreadsheets.

          • David says:

            LOL, multiplying by 1.330045 seems to fix it.

  2. Bob Watson says:

    This is a classic example of the difference between actual and displayed precision.

    That is, B1: =52.73*1.33 gives a result of 71.4476… but using a cell format of Currency (with 2 decimal places) it is displayed as $71.45. David expected the final result to be 71.45*63 = 4501.35 but actually it is 71.4476…*63 = 4501.20. This happens because Excel uses the full calculation precision rather than the displayed precision. As an aside, Excel has an option for using the precision as displayed, but I would not recommend using it as the consequences can be catastrophic.

    So, the problem isn’t with the ROUND function in C1. Rather, the result in B1 needs to be rounded. That is, B1: =ROUND(52.73*1.33,2) which gives a result of 71.45000… as expected. We might also want to keep the ROUND function in C1, to avoid any subsequent precision issues.

    More generally, in practice I would never use a formula like =ROUND(52.73*1.33,2) as it includes hard-coded values. It would be much better to put each value in a cell (say 52.73 in B2, 1.33 in B3, and 2 in B4; appropriately labelled so that we know what they are), and then use the formula B1: =ROUND(B2*B3,B4).

    Cheers,

    Bob.

    (Note that the initial post was confusing because it said $7501.35 where it actually meant $4501.35.)

Leave a Reply

Your email address will not be published. Required fields are marked *