Custom Rounding

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

Thanks for the help. I try it out tomorrow.

Reply | Quote

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)))

Reply | Quote

Is that different from =CEILING(,5)?

Reply | Quote

You are quite right! It just goes to show you can teach an old dog new tricks.Thanks.
Peter

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote