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

CATEGORY - Excel Formula and Function VERSION - All Microsoft Excel Versions

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)

Book Store:

VIEW ALL BOOKS

Recommended Books:

Email Tip

Print Tip

Add to My Tips

Rate This Tip



1	2	3	4	5

Rating: 4.31 Views: 34383

gjcase

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.

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

Pete in the UK

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!

Pete in the UK

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!

Sarah

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.

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

Book Store:

Recommended Books:

For Registered Users

For Guest