Wrong Calculations: Why Does Excel Show Wrong Results & How to Fix It

Excel calculates wrong. Yes, in some cases, Excel will return wrong results. You don’t believe me? Then type the following formula into an empty Excel cell: =1*(0.5-0.4-0.1). The result should be 0. But what does Excel show?  -2,77556E-17. This is just a simple example, but when it comes to larger Excel models it can be quite annoying. Especially if you want to compare the result – let’s say you want to check the result with an IF-formula if it equals 0. So what is the reason for these obviously wrong calculations and how to solve it?

What is the reason for wrong result in Excel?

Well, admittedly the deviation from the correct result is quite small. So in many cases you won’t even bother. The reason is quite ‘technical’: According to Microsoft, the reason for this wrong result is the so-called binary format which the numbers are converted to for calculation (more info on Wikipedia).

Let’s try to put it into simple words: Computers only have 0 and 1 to represent any value. Converting 0.1 into a binary number would result in a long ‘zeros-and-ones-number’: .0001100011000111000111

In order to avoid an endless number, Excel would round it at the end. But this rounding of the binary equivalent to 0.1 leads to miscalculation.

How to avoid wrong calculations in Excel

Unfortunately, there is no easy way of avoiding the wrong calculations in Excel. But we have basically two options: using the ROUND formula or an option called “set the precision as displayed”:

Method 1: Using the ROUND formula

The ROUND formula does exactly what it says: It rounds a value up or down. You can set the number of decimals you want to see. Let’s go with the example in the introduction. The original calculation was (no. 1 in above picture):

=1*(0.5-0.4-0.1)

Adding the ROUND formula:

=ROUND(1*(0.5-0.4-0.1),10) 

We round for 10 decimals. No. 3 in above picture shows an example of how to apply the ROUND formula.

Method 2: Set the precision as displayed

Setting the precision as displayed means in this context, that Excel only calculates as good (or bad) as the numbers are shown on the screenshot. Go to File (no. 4), click on Options and then on Advanced on the left hand side (no. 5). Scroll down to ‘Set precision as displayed’ (no. 6) and set the tick. Confirm with OK.

So, which option should you go for? The second options (setting the precision as displayed) seems easier. But be careful: This can lead to wrong results as well. On the other hand, ROUND is quite troublesome to implement. So, I recommend going with ROUND if you are willing to do the effort. Changing the precision of the calculation should be last resort.

Expert tip: Easily insert the ROUND formula

There is a very comfortable way of wrapping the round formula around your existing formula: The Excel add-in ‘Professor Excel Tools’ provides a function for adding ROUND, ROUNDUP and ROUNDUP with just two clicks:

1. Click on the ROUND button in the Professor Excel ribbon.
2. Set the number of decimals and click OK.

You can select as many cells as you like and insert the ROUND formula to all of them at the same time.

Download the free trial here and add the Professor Excel ribbon to Excel.