0

I have a formula in a worksheet I created that seems to be calculating incorrectly. The formula looks for the difference between two values. The formula should always evaluate to zero as it is comparing entered sales figures against the total sales for the department. As you can see from the images, the amount calculates correctly when I step in and evaluate the value of 'WORK SHEET'!H44 (images 1 and 2), but when I step back out to the original formula, it instead calculates the value as ####.239999999999 rather than ####.24. Is there a reason for this? I have gone back and checked all of the individual figures and they all end at two decimal places, and the other formulas that are used are all addition and subtraction.

Is there something I may be overlooking? I apologize about redacting part of the figures, but my boss is paranoid about information leaking online.

Step 1 Step 2 Step 3

JDay81683
  • 27
  • 3
  • 1
    `'WORK SHEET'!$H$44` seems to be a formulated cell, wrap it within a `ROUND()` function, try `=ROUND('WORK SHEET'!$H$44,2)-'DAILY SALES'!C28` – Mayukh Bhattacharya May 09 '22 at 18:10

1 Answers1

0

This isn't a Microsoft bug, this is the problem with how the computer industry does floating point math. Basically, because of how fractional parts of numbers are stored and handled, there will be error with many, many non-whole values. Extremely tiny errors, but still there.

See https://learn.microsoft.com/en-us/office/toubleshoot/excel/floating-point-arithmetic-inaccurate-result for more about Excel specifically and their recommendations of how to mitigate it, or search the internet for "floating point error" for more information in general.

JSmart523
  • 2,069
  • 1
  • 7
  • 17