0

I hope some of you can help me understand what's going on here, cause i'm puzzled.

I have two cells BH284 and BH287 in an Windows Excel sheet.

When I look at the two cells the values are identical down to last digit (20+ digits)

= BH284 ;output=46.25165516264020000

= BH287 ;output=46.25165516264020000

When I subtract them, I get zero:

= BH284-BH287 ;output=0

My problem is that I have this IF-statement in a cell which outputs a different value when subtracting the two cells:

= IF((BH284-BH287)<0,0,(BH284-BH287)) ;output=7.105427357601E-15

Is there something I'm missing? Is it related to numerical precision somehow? Thanks in advance.

Excel version 14.0.7214.5000 (32-bit) running on Windows 7 Enterprise, service pack 1.

Christian Kjær
  • 1
  • 2
    http://www.cpearson.com/excel/rounding.htm – QHarr Dec 04 '18 at 14:43
  • 1
    You get zero, but that's because Excel "helpfully" turns near-zero results into real zeroes -- but only sometimes. [This article](https://support.microsoft.com/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel) describes FP in Excel in general, but there's another (which, annoyingly, I can't find now) that describes the round-to-zero behavior that's intended to reduce the surprise when subtracing "equal" numbers, but can cause even more surprises in expressions where this doesn't happen. – Jeroen Mostert Dec 04 '18 at 15:03

1 Answers1

0

Yes, you calculated the same number in two different cells and ran into the numerical precision. The Pearson link in the comment is good for detail.

if you want to avoid that there are (at least) two options:

1) format the cell as a number (e.g. 0.00) and the result will appear to be zero.

2) use Round(BH284-BH287,8) this truncates the result at 8 digits behind the decimal point. Normally this should not significantly change the outcome of your calculations. You can go to 13 or 14 digits before you get the same problem again...

vbAdder
  • 659
  • 5
  • 12