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.
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Add more than 120 great features to Excel!
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:
- 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.
Download the free trial here and add the Professor Excel ribbon to Excel.
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
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!
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.
Though this thread is 4-1/2 years old, I have to thank Joe and this site as well as (re)alert others:
** Yes, Excel can absolutely and overtly calculate incorrectly…and Joe’s solution of deleting and re-entering the formula fixed the issue for me. **
This was particularly egregious as it was a simple (A-B)/B calculation, and I watched it (over and over) calculating the wrong number as I stepped through the “Evaluate Formula” debugging tool while looking at my (annoyingly created) replicated test subset file and shaking my head in wonder and disgust.
I must say that over the past 2-1/2 years of intense usage I have been mortified at the bugs within Excel; I wish I had catalogued the list though did not, as each time I thought “Well, this must be a singular but noteworthy exception”. Over two punishing days I had encountered a “Dirty Dozen” but was scrambling because of a deadline and…you know…assuming that fundamental features would/should always work.
One example that I do recall (documented various places) is the handling of simple Boolean algebra within Conditional Formatting, where unexpected results can require “tricking” it by re-ordering and tweaking multiple conditions.
Final vent:
Microsoft’s long history (OS, docs, Azure, et al) can be summed up in two words: Copycat Monopolist.
Just as Intel needs AMD and Nvidia, Microsoft needs competition and regretfully Google seems to have ceded the enterprise to them. As measured by their unfair share of the PC “wallet” over the years, MS has taken oh so much and often given the minimum.
Here’s some illustrative humor lifted from social media that sums it up nicely (regarding Microsoft Word):
“gotta respect the longevity of microsoft word. nothing about it works and it’s still the standard. want to move an image? go to hell. edit a pdf? edit your expectations. ignore a spelling mistake? how about suck my ****. that’ll be 150$ [sic]”
This was help me to save my time. Thank you for tuto. Cheers.
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
01-jan-2018 is not a text. It is the number of days after 0/1/1900 = 43101. Dates are numbers and formatting them as days does not change anything: they are numbers, and are different from what you “see” formatting. Excel always uses the real content of a cell, not the formatted one, in calculations.. if you take four RIGHT characters it converts that real number 43101 in text and takes 3101 (I don’t think 3209, or maybe you changed options in Excel or use an old Apple faulty PC – they use a different and useless standard for date-number). The solution is
RIGHT(TEXT(A24,”dd-mmm-yyyy”),4)
you have not to “format” the cell, you have to calculate cell value formatted as text with TEXT function.
This is such a big help! Thank you so much.
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
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
Hi…yes..simple multiplication…
111111111
x 111111111
Correct answer is 12345678987654321 but
MS Excle gives wrong answer 12345678987654300.
Check it out….and comment….
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.
The roundup function worked for me!! Thought I was going crazy with excel giving wrong numbers!! Thank you!!
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
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
I understand if its some kind of big numbers or difficult calculations, but in my case, I’m trying to subtract 96,00-95,04 and it gives me 0,959999999999994
instead of 0,96. anyone can calculate it in their mind, 1,00 – 0,04 equals 0,96. right? But in excel it says 0,959999999999994
I have a list of about 64 whole number in a spreadsheet. I have used the @sum to total these numbers. In a separate spreadsheet, I have reproduced this column and am coming up with a different total, a difference of 634. There are two numbers 634 in the list. I can take them out and put them back in and am still getting a wrong answer. I have finally added them on an adding machine and found that one of the answers is correct. I recreated both columns using copy and paste (columns now side by side). The numbers are identical but yield different totals. I am at a loss to figure out why this is happening.
I have similar problems, but the miscalculations started only after I reached a certain amount of information and calculations on the sheet. Maybe its a memory handling problem.
If I were Micro Soft I would be very embarrassed. How could they let this out from development stage?
How can anyone have any confidence in their work. I strongly suspect it is very poor design in handling data and algorithmic stratagem on how to correctly handle floating decimal points and mantessa calculation overflow. I used an old, I mean 17, year old QuottroPro program and there is no problem.
Excel is far removed from Excellence.
A2 B2 C3 Formula Excel Results Calculator Results
70.8 64 58.6 =A2 – B2 / C2 69.71 0.97
need help with this formula I need the calculator results to be the same with the excel results
I have a fairly complex formula (=IF(Q171>0,0.75*Q171,IF(AND(ISFORMULA(E171),ISFORMULA(M171),ISBLANK(Q171)),0,IF(OR(ISFORMULA(E171),E171=$S$5),0,MAX(E171,M171)))) with E171=180 and M171 is a formula and Q171 is blank. If you click on the Function button next to the formula it say the result is 180, but it is entering 0 in the cell. Does anyone know why?
it cant help me but i do it
Excel even gets its power calculation wrong.
ie What is 10e6? Type 6. Then do a= power 10 calculation- result 60466176. Clearly nonscence.
Have to enter =(POWER) but the number has to be 10 and the power 6 (ie wrong way round) before you get the right answer of 1000000.
Hi Andreas,
I’m sorry, I can’t follow you. Can you say exactly what to type in which cell to follow up the exmample?
Thanks and best regards,
Henrik
Thank for this tip, it was helpful 🙂
wow, microsoft, even a simple calculator is better than your so called programming
I have had trouble with Excel being 1-3 pennies off in simple calculations, and nothing seems to solve it. I’m looking at a simple addition problem where I can see that $288.03 + $10,012.24 does not end with a “6” but with a “7”…Excel can NOT do this simple addition problem correctly no matter what I try. I’ve re-entered the formula. I’ve entered the formula in a different cell that hasn’t had a formula in it before. I’ve made sure all the numbers are values and not the result of some other calculation. It’s so frustrating!
Wow My calculations were really complex and long and the final result was off by 8-9 figures.Thankyou for the tip. I got my peace of mind back.
I was able to fix this by selecting the option “automatically insert decimal point ” under the advanced tab in options. I’m not sure why that fixed it as all the individual cells were already formatted to 2 decimals. All of my calculations are basic math. It should be easy, yet Excel would constantly return an answer that was off by .5 to 1.