Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Excel can calculate the wrong results: WARNING

Excel calculates the wrong result - Featured Image

The old proverb is that “a bad workman blames his tools”. As Excel is such a popular tool, it gets a lot of blame when the person using it is actually at fault. However, it is not always a person who is to blame; Excel can calculate the wrong results all by itself!

In this post, I want to explain the cause of the problem and provide methods to avoid it.

Watch the video


Watch the video on YouTube.

Spreadsheet errors

The much-quoted statistic is that 80% of spreadsheets contain errors; therefore, it is not uncommon to find an error in an Excel workbook. The errors referred to in this statistic are all caused by humans. Let’s face it, spreadsheets are complex. They are small applications that do not get the rigorous testing they deserve. Therefore, we should expect errors.

Errors tend to fall into 3 groups:

  • Logic errors – caused by the individual not fully understanding the situation and therefore choosing the wrong formula or solution
  • Omission errors – caused by cells and ranges accidentally excluded from formulas
  • Mechanical errors – caused by data inaccuracy or input/typing errors

There is one other error type – the error where Excel doesn’t calculate the correct numbers.

Scenario

Let’s use a basic scenario. Look at the screenshot below:

Excel Calculates the Correct Value

The formula in Cell F7 is:

=VLOOKUP(F5,B5:C7,2,FALSE)

This is a basic VLOOKUP function. The formula looks up at the value 20.4 in Cells B5:B7 and returns the corresponding value from Cells C5:C7.

The value returned is Bravo, which is correct.

Now take a look at the second screenshot.

Excel can calculates the Wrong Result

It appears to be exactly the same. Same lookup value, same data, same formula. But, a different result in Cell F7. The value returned is the error #N/A.

The second example proves that Excel can calculate the wrong result!

The only difference between the two scenarios is in Cells B5:B7. In the first screenshot, the values are all hardcoded:

  • B5: 20.3
  • B6: 20.4
  • B7: 20.5

In the second screenshot, the values are calculated:

  • B5: 20.3
  • B6: =B5+0.1
  • B7: =B6+0.1

This is not an issue with VLOOKUP; the same problem occurs with INDEX/MATCH and XLOOKUP.

In this scenario, we have used VLOOKUP with an exact match; therefore, it returns the #N/A error. However, with an approximate match, it would not have produced an error, but the result “Alpha”. Therefore, the impact of this could be hidden and significant in some scenarios.

Why does this issue occur?

Why does Excel have this issue of calculating different results for the same calculation?

We are taught at school that some numbers cannot be expressed as decimals. For example, one-third (1/3) cannot be expressed as a decimal; it is 0.3333… recurring into infinity. So, instead, we might use 0.33 as a close estimate. We fully accept that 0.33 does not equal 1/3 but deem it insignificant.

Computers calculate numbers using binary. In the background, a conversion takes place to change binary to decimal so that we humans can understand it. As you can see below, it is easier to understand a decimal than a binary number.

Decimal: 237 = Binary: 11101101

Every whole number can be converted from binary perfectly. But this isn’t true for every decimal floating point number.

Taking an example, one-tenth (1/10) is easily expressed as a decimal: 0.1. But. 0.1 in binary is 000110011001100110011… recurring into infinity. Just as we cannot correctly write one-third in decimals, one-tenth cannot be stored in binary.

Computers have a finite amount of storage; therefore, they cannot store an infinite number. In addition, Excel is programmed to calculate accuracy to 15 digits of precision. As a result, Excel can never calculate 0.1 perfectly.

Heading back to our example, if we loaded cells B5:B7 into Power Query, it shows us the underlying number.

Number Precision in Excel shown in Power Query

20.3 + 0.1 is not exactly 20.4, but calculated as 20.400000000000002.

But wait, take a few seconds to count those digits. The screenshot shows it’s not 15 digits of precision, as Microsoft claims in their specifications documents, but the number is 17 digits long.

OK, let’s try something else. Look at the screenshot below.

TRUE shows the numbers as the same

The formulas in each cell are as follows:

  • Cell B1: 20.4
  • Cell C2: =20.3+0.1
  • Cell D2: B2=C2

We’ve seen that 20.3+0.1 = 20.400000000000002.

Therefore, the result in D2 should be FALSE. The VLOOKUP result above tells us that these two are not the same. Yet, the result in D2 is TRUE.

We can conclude from this that some Excel functions calculate using 15 digits of precision while others use 17.

NOTE: 0.1 is not the only number to have this issue, but it is the easiest to explain.



Why don’t they fix the problem?

If it’s a known issue, why doesn’t Microsoft fix it? Unfortunately, it’s not that simple.

In 1985 the Institute of Electrical and Electronics Engineers issued IEEE 754, the Standard for Floating-Point Arithmetic. The standard details how floating point calculations are performed.

Microsoft Excel is designed to the IEEE 754 standard (with a few minor amendments for infinite numbers). However, it’s not just Excel that uses this standard; thousands of software programs also use the same standard.

Therefore, the problem is not necessarily with Excel. Equally, the problem is not with the IEEE 754 standard either. It’s just the complex nature of the world of mathematics and computing that we live in.

Microsoft are open about this issue. It is detailed in their troubleshooting documentation. But I’m guessing 99.9% of people have never read it.

How can we avoid the problem?

Microsoft recommends two options for dealing with this problem (1) Using the ROUND function, or (2) applying precision as displayed.

I don’t particularly like either option (I would prefer it just to work). But, since precision as displayed has a destructive impact on workbook accuracy, I always recommend using the ROUND function.

Using the ROUND function

Heading back to our example, we get the correct result if we change the formulas in Cells B6 and B7 to include the ROUND function.

The formula in Cell B6 rounds the calculation to 1 decimal place.

=ROUND(B5+0.1,1)

Applying precision as displayed

WARNING: Before implementing the precision as displayed solution, be aware that any numbers in your workbook will automatically round to their current displayed value. So, if the original number is 20.44, with the cell set to display one decimal place, the number will become 20.4; The 0.04 will be lost forever. If you must use this option, make sure you backup before continuing.

To apply Precision as Displayed:

  • Click File > Options to open the Excel Options dialog box
  • In the Advanced section, ensure set precision as displayed option is checked
  • A warning message states that data will permanently lose accuracy; if you wish to proceed, click OK.
  • Click OK to close the Excel options.
Set precision as displayed

The formula will now calculate the correct result.

Wrap-up

Due to the nature of calculating floating point numbers, not all decimals can be perfectly expressed in binary. As a consequence, Excel can calculate the wrong result.

Excel applies the IEEE 754 standard to define how floating point numbers are calculated. Therefore Excel, like other applications, can sometimes calculate incorrect results. This is the complex world of mathematics and computing we all live in, rather than an issue with Excel.

Please beware of this issue. Make sure you check your results and, if necessary, apply the ROUND function to avoid the potential errors caused by calculations with floating point numbers.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:


8 thoughts on “Excel can calculate the wrong results: WARNING

  1. Piaskun says:

    Great post with clear explanation.
    I have one unclarity. Couldn’t MS align all functions to have the same precision, i.e. 15 or 17 digits? So Excel would give the same output for both functions from your example.
    Our am I missing something?

    • Excel Off The Grid says:

      Not necessarily. It would depend on which specific numbers are being used.

      Anecdotally, I’ve heard some functions have been adjusted over the years to ensure that insignificant rounding differences like this are automatically ignored (but no idea how true that actually is). But then that doesn’t help people who want to calculate to 15 digits.

  2. Rick de Groot says:

    Mark,

    Another great article that came at a good time.

    The other week I believe I experienced this exact issue. We worked with some datetime values, added an n number of days to it, and performed some checks to see whether a value was equal to another datetime value.

    For some reason, even though the values were looked identical, I never got a match either.

    The rounding of the value also saved our issue there. One really starts to question their abilities for logical reasoning at these moments.

    Thanks for the thorough explanation

    Cheers
    Rick

    • Excel Off The Grid says:

      I’m sure programmers are very used to these types of issues, But if we’re coming from Finance / Engineering / Science / etc, then we just want it to work. Thankfully you spotted you had an issue and were able to fix it 👍

  3. David N says:

    Another option (and another reason to prefer INDEX-MATCH) would be to instruct the formula itself to seek the rounded result. This avoids having to change the original values in any way and can be entered without Ctrl+Shift+Enter in Excel 365.

    =INDEX(C5:C7,MATCH(ROUND(F5,1),ROUND(B5:B7,1),0))

    • Excel Off The Grid says:

      Hi David – A great suggestion as always. You’re right it might be much easier to fix in the formula than in the source.

Leave a Reply

Your email address will not be published.