This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I'm calculating a simple spreadsheet but the total of two cells is repeatedly out by 0.01
I have tried simplifying it in several ways eg. =+F56+J56
I have also tried SUM of the two cells
I have ensured the cells are number not text, and also double-checked the rounding from those cells - if anything it seems to be rounding down!
The original two cells are formatted and calculating to four decimal places, the total cell is formatted to two decimal places
When I use a calculator I get the correct two decimal place total
Please help, this is driving me crazy
Please provide full details on this problem, what are the two numeric values being added and what is the incorrect output resulting from the calculation.
Displayed:
6.54 and 7.39 (actual cell content =+E54 multiplied by G54 and =+I54 multiplied by K54)
in total cell 13.92 (actual cell content =+F54+J54)
I cannot show the asterisk as the multiplication symbol
This is caused by a bug in Excel known as the 'floating-point imprecision' bug, it is caused by the displayed value and the actual value in cells being different in complex calculations, you indicate the original cells are calculated to 4 decimal places, if you set those to two decimal places, does Excel then calculate correctly.
Sadly not.
I did not think that my calculation was complex at all. It has worked ok on some of the same calculations on different lines. This is really awful as I now cannot trust Excel to work correctly for me and the spreadsheet I am working on is part of a court claim! Might sound ridiculous but it has to be accurate to the penny. Thank you for trying to help me though, much appreciated
What are the values in the other cells (E54, G54, I54 and K54)
E54 is =+C54-B54
G54 is 2.1796
I54 is =+E54
K54 is 2.462
I tried altering G54 and K54 to two decimal places too, but it did not make a difference
E54 is =+C54-B54 (these are manually entered numbers)
From what I can gather, it is the results from both the E54 X G54 and the I54 X K54 calculations that are causing the floating point error
Thank you DaveM121.
I may have to reduce/rethink my methodology and see if I can make it work for me.
Really appreciate your time, thank you.
Glad to help!