VBA Double vs Single rounding

Question

in my MS Excel I have a different behavior of rounding operations for different data types:

In the case of Single:

? Application.Round(6.575!, 2) 6.57 

In the case of Double:

 ? Application.Round(6.575#, 2) 6.58 

Does anyone know why?

---

UPDATE : @paxdiablo gave an excellent answer, so just to play it back soon here:

In Single precision, 6.575 is 0 10000001 101001 0011 0011 0011 0011 in binary representation, or 6.57499980926513671875. And this round to 6.57

In double precision, more than 0011 is added, and their weight is enough to make the number a little larger than 6.575 (6.57500000000000017763568394003), and therefore it is rounded to 6.58

Answer 1

Probably due to different floating point precision limits. When you use a value like 6.575 , the computer selects the closest approximation. For a single, it could be:

 6.574999993 

which will be rounded. For double, it could be:

 6.57500000000001 

which will be rounded.

---

Refinement, IEE754 single point bit value for 6.575:

 s eeeeeeee ffffff ffff ffff ffff ffff f 0 10000001 101001 0011 0011 0011 0011 0 

(a repeat of 0011 at the end is usually a sign of an infinitely repeating value, one not exactly representable).

The double is also very similar, it has a bit:

 s eeeeeeeeeee ffffff ffff ffff ffff ffff ... 0 10000000001 101001 0011 0011 0011 0011 ... 

which also has a repeating sequence (see below).

The reason it cannot be accurately represented is that you can only do this, this number can be constructed by summing two (e.g. 4, 2, 1/16, etc.) numbers of valid bits.

As a rule, you can’t always trust what is printed, because print programs know and tune for limited accuracy. Thus, you will almost certainly get 6.575, since this is the closest decimal value for a given bit pattern, but the bit pattern itself will be used for calculations.