0

I experienced some weird calculation using value from other columns after using ROUND() and ROUNDDOWN(). The end result of calculation between these 2 values wasn't expected.

I've ported over the issue I've faced to Google Docs and the end result is similar. You can see the spreadsheet here.

First I simply give a float value, then I ROUND() it, then I ROUNDDOWN() the ROUND() value. Finally, I will take ROUNDDOWN() value and minus it with the ROUND() value.

The end result included quite many decimal places and the value wasn't even close to the initial value I specified which is really weird. See the spreadsheet linked above to get a better idea.

The expected result should be 0.04 or -0.04. Not with a bunch of 9s.

josephting
  • 2,617
  • 2
  • 30
  • 36
  • This is expected behaviour, and is due the limited precission that can be stored in a floating point variable. See my answer to proposed duplicate – chris neilsen Apr 29 '14 at 04:36

1 Answers1

0

The problem is that the values in column D aren't rounded.

When you round a floating-point value, the result is still floating point. The rounding changes the way it's represented, but it's still a mantissa and an exponent, which when combined correctly, give you a number fairly close to what you want. But not exactly what you want (usually).

If you were to round the values in column D after the subtraction, you'd get what you are expecting.

Paul Hicks
  • 13,289
  • 5
  • 51
  • 78