Disagreement between VBA Round & FormatNumber functions

[JenniferMurphy]

I ran into a little oddity that turned out to be a disagreement between the Round and FormatNumber functions

These results are from the Immediate window in a UDF that is interrupted at a breakpoint. The variable Value is defined as double. Its current value is the result of 5-6 calculations.

?value
  30.437499995 

'If I round it to 8 decimal places, I don't get 30.4375 as expected
?round(value,8)
 30.43749999 

'But if I round it to 7, I do
?round(value,7)
 30.4375 

'But if I round the same value as a literal, I get the expected result
?round(30.437499995,8)
 30.4375 

'The FormatNumber function gets it right at 8 decimal places
?formatnumber(value,8)
30.43750000

'It turns out value is actually slightly larger than 30.43749999
?value = 30.4375
False

?value - 30.4375
-5.00000396641553E-09

The value of Value in the Watch window is also "30.437499995".

What's going on here?

 

[=ODIN=]

Just tossing this out there on the off chance that it’s your culprit. Are you using range.value in any of your calc? If so, try changing it to range.value2 and see if it helps.

 

[JenniferMurphy]

Just tossing this out there on the off chance that it’s your culprit. Are you using range.value in any of your calc? If so, try changing it to range.value2 and see if it helps.

Nope. Value is a time interval on units ranging from seconds to years. In the process of the UDF, it gets converted between units a couple of times. For instance, it might go like this:

Dim Seconds As Double
Seconds = pTime
Seconds = Seconds * SecsPerMin
Seconds = Seconds * MinPerHour
Seconds = Seconds * HrsPerDay
Seconds = Seconds * DysPerMonth
  . . .

Then later, it might get converted back:

Dim Value As Double
Value = Seconds
Value = Value / SecPerMin
Value = Value / MinPerHour
Value = Value / HrsPerDay
  . . .

I think it's pretty clear that this is a rounding error, but I wonder why the Round function gets a different answer than the FormatNumber function.

Thanks

 

[joeu2004]

I wonder why the Round function gets a different answer than the FormatNumber function.

In a nutshell, because it appears that FormatNumber first rounds to 13 decimal places (15 significant digits) for this example, whereas it appears that VBA Round does not.

Note that Round(Round(v,13),8) is indeed 30.4375.

-----

Of course, FormatNumber and VBA Round differ because VBA Round uses "banker's rounding" (round half to even), whereas FormatNumber does not (always round half up).

But that would not make a difference if the value of "v" (what you call "value") were truly 30.437499995.

In fact, "v" is not really 30.437499995. 30.4375 - 30.437499995 is 5.00000041370186E-09. But as you noted, v - 30.437499995 is 5.00000396641553E-09; much less.

Most calculated values with decimal fractions are precise to more decimal places than VBA and Excel displays. Both format only the first 15 signifcant digits (rounded), an arbitrary limitation. It takes 17 significant digits (rounded) to convert between binary and decimal with no loss of precision, as the IEEE standard specifies.

In fact, 30.437499995 is 30.4374999949999,99586298145004548132419586181640625, which rounds to 30.4375000000000,00.

The value of "v" is 30.4374999949999,9603358446620404720306396484375, which rounds to 30.4374999949999,96.

(I use period for the decimal point and comma to demarcate the first 15 significant digits.)

-----

If you need an explanation for why "v" is not 30.437499995 or 30.4375, you will need to tell us the following values:

? Format(pTime, "0.00000000000000E+0")
? pTime - Format(pTime, "0.00000000000000E+0")

That should be 14 zeros after the decimal point.

It might also be helpful to know the origin of pTime. It is not 2629800, despite appearances.

 

[JenniferMurphy]

Thanks for that.

 

[joeu2004]

Glad it was helpful and you understood, despite my typos. For posterity, I should have written (corrections in blue)....

In fact, "v" is not really 30.437499995. 30.4375 - 30.437499995 is 5.00000041370186E-09. But as you noted, 30.4375 - v is 5.00000396641553E-09; so, "v" is much less.

and....

In fact, 30.437499995 is 30.4374999949999,99586298145004548132419586181640625, which rounds to 30.4374999950000,00.