Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

math - VBA Double vs Single rounding

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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).

enter image description here 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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...