VBA: How to round to 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 abitrary number?

By 5:

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

By 10:

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

etc.

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][1], 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.

[1]: http://blogs.msdn.com/ericlippert/archive/2003/09/26/bankers-rounding.aspx Bankers Rounding

Answer 1

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

round(X / N)*N

Answer 2

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

Answer 3

something like that?

'nearest
 n = 5
 'n = 10

 'value
 v = 496
 'v = 499 
 'v = 2348 
 'v = 7343

 'mod
 m = (v \ n) * n

 'diff between mod and the val
 i = v-m


 if i >= (n/2) then     
      msgbox m+n
 else
      msgbox m
 end if

Answer 4

Simply ROUND(x/5)*5 should do the job.

Answer 5

I cannot add comment so I will use this

in a vbs run that and have fun figuring out why the 2 give a result of 2

you can't trust round

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

Answer 6

For a strict Visual Basic approach, you can convert the floating-point value to an integer to round to said integer. VB is one of the rare languages that rounds on type conversion (most others simply truncate.)

Multiples of 5 or x can be done simply by dividing before and multiplying after the round.

If you want to round and keep decimal places, Math.round(n, d) would work.

Answer 7

Thank you everyone for all your answers. I'm the smarter for it. Sorry I can only mark a single ressponse as correct, as the real "answer" was scattered across several messages. When I get enough reputation points I'll mark them up. :)

-matt