#### Wrong Calculations – Why Does Excel Show a Wrong Result?

**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”:

- 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=1*(0.5-0.4-0.1)

1=ROUND(1*(0.5-0.4-0.1),10) - Set the precision as displayed: That would mean, that Excel only calculates as good (or bad) as the numbers are shown on the screen. 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:

- Click on the ROUND button in the Professor Excel ribbon.
- 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.

**This function is included in our Excel Add-In 'Professor Excel Tools'**

Learn more Download Free Trial

(No sign-up, download starts directly)**More than 10k people on Facebook can't be wrong.**

## Comments 12

## Bill Young

I have been using Excel for roughly 20 years. I have a degree in electrical engineering so I can handle pretty much any math needed in Excel. I am doing what is known as loss development. Essentially I need to get the precise number of years between two dates. Precise, even fractionally precise. I figured Excel’s YEARFRAC function did this. It doesn’t. For example =YEARFRAC(“8/1/2011″,”7/31/2012”) and =YEARFRAC(“8/1/2011″,”8/1/2012”) both return precisely 1.00000 as the answer (absolutely identical results, verified using =(A2=B2) where the results of the YEARFRAC formulas are stored in A2 and B2). Frustrating!

## Joe

I had a different calculation problem. I was subtracting a cell that was the calculated sum of expenses from a cell that was the calculated sum of income. Excel gave me a result of $0, even though there was a difference between income and expenses. What fixed the problem was deleting the formula in the calculated expenses cell and then re-entering it. The new formula was exactly the same, so I have no idea why Excel didn’t come up with the right answer the first time. Good thing it was an error that was easy to catch.

## MD40

This was help me to save my time. Thank you for tuto. Cheers.

## Sudhir Pani

When I am using RIGHT or LEFT formula, i am getting incorrect results.

Below is the example I am getting the error as,

Ex. in the cell A2 which has a date as 01-Jan-2018

When I am applying the formula as =RIGHT(A2,4),I should get the result as 2018, however it is showing me as 3209

I also set the formatting on date, however nothing is working for me.

Please help

Thanks

## Charyen Daymiel

This is such a big help! Thank you so much.

## Silvano Rego

This little calculation gives me an error

6356752,31424518^2

in excel = 40408299984661,50000000000000000000

in other calculators = 40408299984661,4516607531532324

How can this be possible?

help please.

Silvano

## Peter

I assume this is why I get an odd result when I add the following list of numbers (in this order). Using ‘SUM’, gives the total 5418.54. Displaying the total to 11 decimal places gives me 5418.54000000001.

However, calculating it as (27 x 87.02) + 3069 gives the result 5418.54 with no extra decimals.

Oddly though, adding the same numbers in a different order, removes the extra decimals. Using ROUND also works.

3069

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

87.02

## Sarika

Hi…yes..simple multiplication…

111111111

x 111111111

Correct answer is 12345678987654321 but

MS Excle gives wrong answer 12345678987654300.

Check it out….and comment….

## War Consigliere

Excel even struggles with simple addition. It insists on telling me that -28.96+25+5-1+0.02-0.06 = -8.46545E-16. There shouldn’t be any rounding involved and it shouldn’t be storing more than two decimal places through the sum.

## SpacePanda

The roundup function worked for me!! Thought I was going crazy with excel giving wrong numbers!! Thank you!!

## John

Excel calculate wrong. Basic operation without roundings: 5-1

(6-7). Calculus order is 1. Bracks (=-1) then> Multiply (-1-1 ==1) then subtraction 5-1 and the result is 4. Excel claim, it is 6. EXCEL DOES NOT KNOW CALCULUS ORDER## trendfriendpa

What iswrong with this formula?

=D7*IF(OR(C7=”BC”,C7=”BP”,C7=”SC”,,C7=”SP”),(H7-F7),(F7-I7)-(E7/100))

C7 = type of purchase/sale

H7 = ASK price

I7 = BID price

F7 = entry price

D7 = profit/loss

E7 = total cost to purchase/sell divide by 100

The F7-I7 is giving the wrong result

.83 minus .50 = .33 but it is giving -.33

The answer should be when formula runs

83-50 = 33

But result shows -33