I was working with numbers in the form A * B / C (all Long, and C factorises B), and because A and B were relatively large I was getting some overflow errors.
Actually, from a numerical analytic point of view, A*B/C is the more reliable form, as long as A*B is not likely to exceed 2^53 (9,007,199,254,740,992).
In general, do as much integer (non-floating point) arithmetic as possible.
With the constants you mention, the simple solution was:
1260
# * 7470 / 113400
or even
1260
& * 7470 / 113400
or use the CDbl or CLng conversion functions.
(Generally, I prefer type Double in such cases because it has a larger numerical range: 2^53 v. 2^30 for type Long.)
Otherwise, VBA uses type Integer arithmetic for 1260*7470 because 1260 and 7470 are small enough to be type Integer.
Without really thinking about it, I changed to A / C * B, and very ocasionally (hence a pain to debug!) started getting rounding discrepancies. At its simplest, try this in the Immediate Pane:
?7470/113400*1260 Answer 83 (same in Excel)
?INT(7470/113400*1260) Incorrect answer 82 (83 in Excel)
[....]
So just curious, is this a known issue, or has anyone encountered similar?
Great find!
it's just one of the joys of floating point arithmetic [...]. you'll need Currency or Decimal data types to catch the floating point rounding error.
dDouble = 83 - 7470 / 113400 * 1260 reports the discrepancy as 6.93889390390723E-18
Both type Currency and Decimal have their own set of issues. I am not a fan of trading one set of problems for another.
Although A*B/C is the better order of operation, in general, you could have avoided this particular problem with:
? Int(
CDbl(7470/113400*1260
))
or
? Int(
CDbl(7470/113400
)*1260)
The latter is consistent with the way that Excel evaluates arithmetic expressions.
The issue arises because Intel-compatible CPUs actually use an 80-bit binary floating-point form for
performing arithmetic. (They use 64-bit binary floating-point for
storage.)
VBA tries to make use of the 80-bit intermediate results. Excel does not.
With your example, the problem is: even though 1260*7470/113400 is factorable into an integer result, 7470/113400 is not.
The 64-bit result is 0.0658730158730158,7491030119281276711262762546539306640625. And the 64-bit result of CDbl(7470/113400)*1260 is exactly 83, by coincidence.
(Comma demarcates the first 15 significant digits.)
But presumably, the 80-bit result of 7470/113400 is sufficiently different that (7470/113400)*1260 is infinitesimally less than 83; as you noted: -6.93889390390722,8377647697925567626953125E-18.
In the world of floating-point arithmetic (and that includes type Decimal), we really cannot and should not rely on any of this. Instead, we must rely on "good" numerical analytic principles.
If you want to emulate Excel arithmetic, convert each pairwise arithmetic operation to 64-bit binary floating-point by using CDbl.
That is not always the "best" result; just results that are consistent with Excel -- usually.
I say "usually" because Excel INT, for example, has its own set of problems.
For example, =INT(0.999999999999999 + 6E-16) returns 1, even though
=(0.999999999999999 + 6E-16)-1-0 formatted as General demonstrates that
0.999999999999999 + 6E-16 is indeed less than 1. So VBA Int(0.999999999999999 + 6E-16) is correct to return 0.
(The "redundant" -0 is needed to circumvent another Excel anomally: if an arithmetic reduction is "close enough" to zero,
sometimes Excel replaces the actual arithmetic result with exactly zero. The operative word is "sometimes".)
