How do I round up or down to nearest 5?

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
This must be simple, but with ROUNDUP and ROUNDDOWN, I can round up or down to nearest 10 or multiple of 10 by using -1, -2, -3 etc. as the second argument.

But how do I round up or down to the nearest 5? For example, 862 to 860 and 863 to 865?

To the nearest 50? 1210 to 1200 and 1230 to 1250?

Thanx in advanx.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
see help for MROUND.
Should do the trick!
mb
NOTE: If I recall, you may have to install this formula from disk, isn't part of standard install...
 
Upvote 0
On 2002-03-13 11:52, Barry Katcher wrote:
This must be simple, but with ROUNDUP and ROUNDDOWN, I can round up or down to nearest 10 or multiple of 10 by using -1, -2, -3 etc. as the second argument.

But how do I round up or down to the nearest 5? For example, 862 to 860 and 863 to 865?

To the nearest 50? 1210 to 1200 and 1230 to 1250?

Thanx in advanx.

=ROUND(A1/5,0)*5

MROUND can do the same.
 
Upvote 0
On 2002-03-13 11:52, Barry Katcher wrote:
This must be simple, but with ROUNDUP and ROUNDDOWN, I can round up or down to nearest 10 or multiple of 10 by using -1, -2, -3 etc. as the second argument.

But how do I round up or down to the nearest 5? For example, 862 to 860 and 863 to 865?

To the nearest 50? 1210 to 1200 and 1230 to 1250?

Thanx in advanx.

A Tip: There's a "See Also" link in the upper left hand corner of the Excel Help topics for "ROUNDUP worksheet function" and "ROUNDDOWN worksheet function". This would have steered you to MROUND.
This message was edited by Mark W. on 2002-03-13 11:59
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top