# Curious VBA rounding using INT and Long variables

#### StephenCrump

##### MrExcel MVP
When answering a recent post here about combinations, I encountered a curious VBA rounding discrepancy ...

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.

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)

Easily fixed with:
?INT(7470/(113400/1260))

but I totally wasn't expecting this glitch.

So just curious, is this a known issue, or has anyone encountered similar?

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I haven't checked it as just going to work you are probably finding the difference between Bankers rounding (what VBA uses) and normal rounding (what Excel uses).
Google "Bankers rounding" or "Bankers rounding Excel Vba" or see the link below for basic description.

Mark

Rather than a ROUND() issue, it's just one of the joys of floating point arithmetic, and probably one that I should have anticipated. Clearly with Int(calculation), the number isn't resolved until Int() has been applied.

7470 / 113400 * 1260 resolves as 83 to more than 15 significant figures, so 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

Yep and that should have been my first thought
At least you found a reasonable explanation

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

Great find!

Yes, an embarrassing case of premature posting.

Thanks joeu2004 for your comprehensive response, and thanks again Mark. I'm going to slink away quietly now.

Replies
4
Views
744
Replies
3
Views
246
Replies
0
Views
304
Replies
1
Views
893
Replies
6
Views
2K

1,196,252
Messages
6,014,265
Members
441,809
Latest member
pawansher2002

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back