1. ## 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

2. ## 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

3. ## Re: Custom Rounding

Thanks for the help. I try it out tomorrow.

4. ## 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) ))

5. ## Re: Custom Rounding

Is that different from =CEILING(<reference>,5)?

6. ## Re: Custom Rounding

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

7. ## 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!

8. ## 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>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•