🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Round in Excel — Banker's Rounding and Why It Differs From the Worksheet

|

VBA Round in Excel — Banker's Rounding and Why It Differs From the Worksheet

TL;DR — VBA's Round is not the ROUND you use in a cell. It rounds a half to the nearest even digit (banker's rounding), so Round(2.5) is 2 and Round(3.5) is 4 — halves do not always go up. The worksheet's ROUND rounds halves away from zero (2.5 → 3). When a macro total has to agree with the sheet, call Application.WorksheetFunction.Round instead of the native Round.

Sub RoundingIsNotWhatYouThink()
    Debug.Print Round(2.5)                              ' 2  - half to EVEN, not 3
    Debug.Print Round(3.5)                              ' 4  - half to EVEN
    Debug.Print Round(2.5, 0)                           ' 2  - same rule with a digits argument
    Debug.Print Application.WorksheetFunction.Round(2.5, 0)  ' 3  - matches =ROUND(2.5,0)
End Sub

Round, Mod and Rnd are three math tools that look like their worksheet cousins but quietly behave differently, because VBA is a general-purpose programming language, not the spreadsheet. Rounding is the one that costs money: get the rule wrong and a macro's totals drift a cent away from the cells everyone else is reading.

What you'll learn

  • The mental model — VBA math is not worksheet math, and Round proves it
  • What banker's rounding is and why Round(2.5) is 2 while the cell gives 3
  • The number-one bug — a macro total that is a cent off from =ROUND() on the sheet
  • How to match the worksheet exactly with Application.WorksheetFunction.Round
  • Why Int and Fix truncate rather than round, and disagree on negative numbers

The mental model: VBA math is not worksheet math

You already know how =ROUND(2.5, 0) behaves in a cell — it gives 3. So the first time a macro prints 2, it looks like a bug. It is not. VBA's Round function follows a different, deliberate rule called banker's rounding (formally, round half to even): when a number sits exactly on the halfway mark, it rounds to the nearest even digit rather than always upward.

Debug.Print Round(0.5)    ' 0   (0 is even)
Debug.Print Round(1.5)    ' 2   (2 is even)
Debug.Print Round(2.5)    ' 2   (2 is even)
Debug.Print Round(3.5)    ' 4   (4 is even)

Notice the halves alternate down, up, down, up. That is the whole point of the rule: over many values, rounding halves consistently upward introduces a small upward bias, and rounding to even cancels it out. It is the statistically fairer choice — which is exactly why a language designed for general computation picked it, and why the spreadsheet, built for everyday arithmetic, did not.

Hold on to one sentence and the rest of this article follows: the function name is the same, the rule is not. Everything below is a consequence of Round rounding to even.

The number-one bug: a total that is a cent off

Here is the failure that sends people searching. A macro sums invoice lines and rounds each to two decimals; the grand total comes out one cent below the =ROUND(...) column the finance team maintains on the sheet, and now the two numbers do not reconcile:

' Rounds every half-cent to even - drifts away from the sheet's =ROUND()
lineTotal = lineTotal + Round(price * qty, 2)

Each individual Round is off by at most half a cent, but across hundreds of half-cent values the even-rounding and the sheet's away-from-zero rounding pull in different directions, and the pennies accumulate. Nothing errors; the numbers simply disagree. When your output has to match a cell, do not use VBA's Round:

' Rounds exactly like =ROUND() on the sheet - half away from zero
lineTotal = lineTotal + Application.WorksheetFunction.Round(price * qty, 2)

Application.WorksheetFunction.Round (you can also write Application.Round) is the worksheet's ROUND, called from VBA, so it rounds halves away from zero and your total reconciles to the penny. The rule of thumb is blunt: money and any figure that must agree with the sheet → WorksheetFunction.Round; leave the native Round for statistics where round-to-even is the point.

Rounding is not truncating: Int and Fix

Rounding decides which way a fraction goes; sometimes you do not want a fraction at all, you want to chop it off. That is truncation, and VBA has two functions for it — which, of course, disagree with each other on negative numbers:

Debug.Print Int(2.7)     ' 2    Fix(2.7)   ' 2    - identical for positives
Debug.Print Int(-2.7)    ' -3   Fix(-2.7)  ' -2   - they split on negatives

Int rounds down toward negative infinity (a true floor), so Int(-2.7) is -3. Fix rounds toward zero (it just drops the decimals), so Fix(-2.7) is -2. For positive numbers they are the same; the moment a negative appears, you have to know which one you meant. Neither is Round — they never look at the fractional part to decide, they simply remove it.

There is also no built-in RoundUp or RoundDown in VBA. When you need "always up" or "always down" to a number of decimals, reach for the worksheet versions: Application.WorksheetFunction.RoundUp(x, 2) and RoundDown(x, 2).

The floating-point footnote

One more surprise lurks under all rounding: Double values are stored in binary, and some tidy-looking decimals cannot be represented exactly. The classic example is Round(2.675, 2), which returns 2.67, not 2.68 — because 2.675 is actually held as 2.67499999..., so it is not really a half at all. This is not a Round bug; it is the nature of floating point. When exactness to the cent matters, work in the Currency or Decimal type, or round through WorksheetFunction.Round, which is built to smooth over these representation gaps.

When banker's rounding is the right call

None of this means VBA's Round is broken — it means it is aimed at a different job. If you are computing an average, a statistical summary, or anything where thousands of halves would otherwise bias a result upward, round-to-even is the correct choice and the worksheet's away-from-zero rule is the biased one. Use the native Round on purpose there. The mistake is not using Round; it is using it for money that has to reconcile with a spreadsheet, where the two rules quietly diverge.

How ExcelMaster helps

Rounding is a decision with three quiet forks — round-to-even versus away-from-zero (native Round versus WorksheetFunction.Round), round versus truncate (Round versus Int/Fix, which then disagree on negatives), and whether floating point is holding a real half at all — and every wrong turn fails silently: a total a cent off, a negative floored the wrong way, a 2.675 that will not round up.

ExcelMaster lets you say what you actually want — "round each line to two decimals so the total matches the ROUND column on the sheet" — and it picks WorksheetFunction.Round because you said match the sheet, uses Int or Fix deliberately when you mean truncate, and flags the money-in-Double trap before it costs you a penny. You keep the workbook and the code.

Frequently asked questions

Why does VBA Round(2.5) return 2 instead of 3?

Because VBA's Round uses banker's rounding — round half to even. When a value is exactly halfway, it goes to the nearest even digit, so Round(2.5) is 2 (2 is even) and Round(3.5) is 4 (4 is even). The worksheet's =ROUND(2.5,0) uses a different rule, round half away from zero, which gives 3. Same name, different rule.

How do I make VBA round the same way as the worksheet ROUND?

Call the worksheet function from VBA: Application.WorksheetFunction.Round(x, 2) (or Application.Round(x, 2)). It rounds halves away from zero exactly like =ROUND() in a cell, so totals computed in a macro reconcile with a ROUND column on the sheet. Reserve the native Round for cases where round-to-even is genuinely what you want.

How do I round to 2 decimal places in VBA?

Pass the number of digits as the second argument: Round(value, 2) for banker's rounding, or Application.WorksheetFunction.Round(value, 2) to match the sheet. To always round up or down to two places, use WorksheetFunction.RoundUp(value, 2) or RoundDown(value, 2) — VBA has no native RoundUp or RoundDown.

What is the difference between Int and Fix in VBA?

Both remove the fractional part, but they disagree on negatives. Int floors toward negative infinity, so Int(-2.7) is -3. Fix truncates toward zero, so Fix(-2.7) is -2. For positive numbers they are identical. Neither rounds — they discard the decimals rather than looking at them, unlike Round.

Why does Round(2.675, 2) give 2.67 and not 2.68?

Because 2.675 cannot be stored exactly as a Double — it is really held as 2.67499999..., so it is not a true half and rounds down. This is floating-point representation, not a rounding bug. For money, use the Currency or Decimal type, or round through Application.WorksheetFunction.Round.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-18.

Related guides: VBA Mod · VBA Rnd · VBA Format · VBA Number Format · VBA WorksheetFunction