Results 1 to 8 of 8

Thread: Custom Rounding

  1. #1
    3 Star Lounger
    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

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

  3. #3
    3 Star Lounger
    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.

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

  5. #5
    Uranium Lounger
    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

  6. #6
    New Lounger
    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

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    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!

  8. #8
    3 Star Lounger
    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>

Posting Permissions

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