Rounding a number to the nearest 5 or 10 or X

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

round(X / N)*N


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


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.

Tags:

Vba

Rounding