VBA Double vs Single rounding
excel, math, vba
Solution
Probably because of the different limits of floating point precision. When you use a value like `6.575`, the computer chooses the closest approximation. For a single, this may be:
6.574999993
which will round down. For a double, it may be:
6.57500000000001
which will round up.
Clarifying, IEE754 single precision bit-value for 6.575 is:
s eeeeeeee ffffff ffff ffff ffff ffff f
0 10000001 101001 0011 0011 0011 0011 0
(that repeating `0011` at the end is usually the sign of an infinitely recurring value, one not exactly representable).
The double is also very similar, it has the bits:
s eeeeeeeeeee ffffff ffff ffff ffff ffff ...
0 10000000001 101001 0011 0011 0011 0011 ...
which also has the repeating sequence (see below).
The reason it cannot be represented exactly is because you can only do that is the number can be constructed by summing powers of two (like 4, 2, 1/16 and so on) within the number of bits allowed for.
Generally, you cannot always trust what gets printed out since the print routines know about and adjust for the limited precision. So you'll almost certainly get 6.575 since that's the closest decimal value to the given bit pattern, but the bit pattern itself will be used for calculations.
Problem
in my MS Excel I have different behavior of rounding operations for different data types: In case of Single: ``` ? Application.Round(6.575!, 2) 6.57 ``` In case of Double: ``` ? Application.Round(6.575#, 2) 6.58 ``` Does anybody know why? UPDATE: @paxdiablo gave an excellent answer, so just to reproduce it shortly in here: In Single precision, 6.575 is 0 10000001 101001 0011 0011 0011 0011 in binary representation, or 6.57499980926513671875. And this rounds to 6.57 In Double precision more 0011 are added and their weight is enough to make the number slightly greater tham 6.575 (6.57500000000000017763568394003), and hence it rounds to 6.58