Rounding to nearest multiple of five

  • Thread starter Thread starter nmitch59
  • Start date Start date
N

nmitch59

I am writing a commission formula that calculates 10% commission.
want to round it to the nearest multiple of 5 eg

Sale is $2,045 and commission is $204.50 - I want it to show $205.
Sale is $2,015 and commission is $201.50- I want it to show $200.

Can someone help??

Thank
 
Use the MROUND formula
=MROUND(cell you want to round,multiple you want to round to

If your number is in A5, and you wanted to round to the nearest 5, the formula would be
=MROUND(A5,5
tj
 
Assuming sales amounts are in A2, A3 downwards,
with commission of 10% is in B1 (i.e. in B1: 10%)

Try in B2: =INT(A2*$B$1/25)*25+IF(MOD(A2*$B$1,25)>2.5,5,0)

Copy B2 down
--

If it is desired to "round-up" amounts
*at* the midpoint between 0 to 5, i.e. "=2.5" to 5
instead of "rounding-down" to 0

just change:

.... MOD(A2*$B$1,25)>2.5 ..
to:
.... MOD(A2*$B$1,25)>=2.5 ..

in the formula
 
Back
Top