Thread: Rounding Up / Down to the Nearest Multiple of a Specific Number

Thanks for this - can you tell me how you round to the nearest multiple, i.e. I want numbers to round to the nearest multiple of 5, so 4.50=5 and 11=10.

Many thanks in advance for any help!

Thanks for this - can you tell me how you round to the nearest multiple, i.e. I want numbers to round to the nearest multiple of 5, so 4.50=5 and 11=10.

Many thanks in advance for any help!

THANK YOU!! I have actaully been trying to figure out to do this for over an hour, Your help was so much better than the offcial page.

Problem:

Rounding the numbers in List1 (column A) to the nearest multiple of 50. We want to round both up and down.

Solution:

To round up, use the CEILING function in the following formula:
=CEILING(A2,50)
To round down, use the FLOOR function in the following formula:
=FLOOR(A2,50)

If you want to overcome the sign issue, i.e., if your number may be either positive or negative and you always want to round to the outside of the range:

=CEILING(A2,50*A2/ABS(A2))

If A2=76, will return 100
If A2= -76, will return -100

For inside the range (closer to zero), use FLOOR.