Results 1 to 8 of 8
Thread: Custom Rounding

20010614, 20:44 #1
 Join Date
 Jun 2001
 Location
 Irvine, California, USA
 Posts
 292
 Thanks
 0
 Thanked 1 Time in 1 Post
Custom Rounding
I have a price list with links from another sheet. I want to round up (ideally up or down) to the closest "0" or "5". For example if the current value is 347.25, I would like it to be 350. If it is 342.75, I would want it to be 345. If it is 340.75, I want it to be 340. If it is 341.25, I want it to be 345. Is this possible? Any help would be appreciated. I am using Office2K sr1/Win2Ksp2

20010614, 21:05 #2
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Custom Rounding
Try the MROUND (Multiple Round), which has the following syntax :
MROUND(A1,X), where X is the number to round to (5 in your case) so that =MROUND(A1,5), would return 345 if A1 contained 347.25. However 341.25, would round to 340 and not 345.
Andrew C

20010615, 05:38 #3
 Join Date
 Jun 2001
 Location
 Irvine, California, USA
 Posts
 292
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Custom Rounding
Thanks for the help. I try it out tomorrow.

20010918, 11:45 #4
 Join Date
 Sep 2001
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Custom Rounding
If you want to always round up to the nearest 5 (for example) try =if(mround(a1,5)<a1,(mround(a1,5)+5),(mround(a1,5) ))

20010918, 15:37 #5
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Custom Rounding
Is that different from =CEILING(<reference>,5)?
John ... I float in liquid gardens
UTC 7ąDS

20010919, 21:27 #6
 Join Date
 Sep 2001
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Custom Rounding
You are quite right! It just goes to show you can teach an old dog new tricks.Thanks.
Peter

20010919, 22:24 #7
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 201
 Thanks
 8
 Thanked 0 Times in 0 Posts
Re: Custom Rounding
I saw your post yesterday and the subsequent one on MROUND, which is in the Analysis Toolpack, which was my initial thought. Your other point about rounding up between 340 and 345 at 341.25 does not seem to have been addressed if I am correct, but it does have a simple solution. If you always add 1.25 to the number to be rounded you will get the correct answer.
Take these examples:
340 + 1.25 = 341.25, then Rounded using MROUND will be 340
341 + 1.25 = 342.25, then Rounded using MROUND will be 340
342 + 1.25 = 343.25, then Rounded using MROUND will be 345
343 + 1.25 = 344.25, then Rounded using MROUND will be 345
344 + 1.25 = 345.25, then Rounded using MROUND will be 345
If you want to round at an offset point just add the difference between the offset point and the normal rounding point and the rounding will work as you require.
I hope this helps.
Good Luck!

20011210, 20:57 #8
 Join Date
 Jun 2001
 Location
 Irvine, California, USA
 Posts
 292
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Custom Rounding
Thanks for your suggestion. In the end I decided to go with the CEILING function and have everything round up to the next multiple of 5. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>