Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rounding to nearest 1/4 (Excel 2007)

    I need to round to the nearest 1/4. For example, 10.37, would round up to 10.5 and 10.81 would round down to 10.75. I know how to use roundup, rounddown, and round. None of those would do the trick. Ceiling and floor don't quite work out either. Perhaps a combination of ceiling and floor, but is there anything simpler? Thanks

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding to nearest 1/4 (Excel 2007)

    <P ID="edit" class=small>(Edited by mbarron on 11-Dec-08 13:48. typo)</P>Try the Mround() function:
    =MROUND(A1,0.25)

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding to nearest 1/4 (Excel 2007)

    Thanks. Exactly what I was looking for. Why doesn't the help file do a related reference in ceiling? That's where I started looking. It's great to have Woody's Loungers around!

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding to nearest 1/4 (Excel 2007)

    It may not show in the "Related" because the MROUND function is part of the Analysis Toolpak add in.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Rounding to nearest 1/4 (Excel 2007)

    Hi Cathy,

    You could also use:
    =ROUND(A1*4,)/4
    That avoids the need for the users to have the Analysis Toolpack installed. Also, according to your specs, 10.37 should round to 10.25 (which is what the above formula does), not 10.50.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding to nearest 1/4 (Excel 2007)

    Even Better! Thanks

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rounding to nearest 1/4 (Excel 2007)

    A GEILING/FLOOR mixed formula, may meet your expected result

    That is, 10.37 round up to 10.5 , and 10.81 round down to 10.75

    Try the formula……

    =IF(MOD(A1,10)>=0.5,FLOOR(A1,0.25),CEILING(A1,0.25 ))

    A testing at the range of value, from 10.01 to 10.99,
    below table show the formula results arising from MROUND and GEILING/FLOOR

    Range of value….MROUND…GEILING/FLOOR
    10.01 to 10.12……..10.00……10.25
    10.13 to 10.25……..10.25……10.25
    10.26 to 10.62……..10.25……10.50
    10.63 to 10.74……..10.75……10.50
    10.75 to 10.99……..11.00……10.75

    Regards
    Bosco

Posting Permissions

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