Rounding a number to the nearest 5 or 10 or X

Question

Given numbers like 499, 73433, 2348 what VBA can I use to round to the nearest 5 or 10? or an arbitrary number?

By 5:

 499 ->  500
2348 -> 2350
7343 -> 7345

By 10:

 499 ->  500
2348 -> 2350
7343 -> 7340

etc.

Solution 1

Integrated Answer

X = 1234 'number to round
N = 5    'rounding factor
round(X/N)*N   'result is 1235

For floating point to integer, 1234.564 to 1235, (this is VB specific, most other languages simply truncate) do:

int(1234.564)   'result is 1235

Beware: VB uses Bankers Rounding, to the nearest even number, which can be surprising if you're not aware of it:

msgbox round(1.5) 'result to 2
msgbox round(2.5) 'yes, result to 2 too

Thank you everyone.

Solution 2

It's simple math. Given a number X and a rounding factor N, the formula would be:

round(X / N)*N

Solution 3

To round to the nearest X (without being VBA specific)

N = X * int(N / X + 0.5)

Where int(...) returns the next lowest whole number.

If your available rounding function already rounds to the nearest whole number then omit the addition of 0.5

Solution 4

In VB, math.round has additional arguments to specify number of decimal places and rounding method. Math.Round(10.665, 2, MidpointRounding.AwayFromZero) will return 10.67 . If the number is a decimal or single data type, math.round returns a decimal data type. If it is double, it returns double data type. That might be important if option strict is on.

The result of (10.665).ToString("n2") rounds away from zero to give "10.67". without additional arguments math.round returns 10.66, which could lead to unwanted discrepancies.

Solution 5

'Example: Round 499 to nearest 5. You would use the ROUND() FUNCTION.

a = inputbox("number to be rounded")
 b = inputbox("Round to nearest _______ ")


  strc = Round(A/B)
  strd = strc*B


 msgbox( a & ",  Rounded to the nearest " & b & ", is" & vbnewline & strd)

View more solutions

Comments

[Vilx-]

Just to clarify: int(N+0.5) is the same as round(N)

[Vilx-]

I'd say that it either has something to do with the way floating point numbers are stored, or a correct implementation of the internationally standartized rounding algorithm. I don't know it's name, but it was so that every other .5 was rounded down, and the rest up.

[mrnakumar]

Vilx, your suggestion is good, but not for VBA round(465 / 10)*10 will return 460

[Alnitak]

Well, that's FP math for you, get over it. Most FP numbers ending in 0.5 will actually be a tiny fraction either above or below the required value, and that affects the rounding.

[paxdiablo]

@Manuel, I think that's called Bankers rounding (nearest even number), one of the MANY variants.

[mins]

This won't round 2348 to 2350 as requested in the question (second example).

[tomRedox]

I think this is VB.NET not VBA? The logic is useful though!