Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Rounding to a quarter... (Excel 2000 >)

    Ever needed to ROUNDUP or ROUNDDOWN or ROUND to the quarter, or any other interval of choice. Here is a great manipulation on the ROUND function...

    Enter a decimaled value in A1and type =ROUND(A1/0.25,0)*0.25 in another cell. You can change the value in the function to round to the interval of choice.

    PS: Of stead of searching for the function using the Paste Function Dialog (Fx button), simply type: =FunctionName and press Ctrl + A to activate the pallette automatically.

    Cheers
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rounding to a quarter... (Excel 2000 >)

    That will work fine.

    The Analysis ToolPak add-in has a dedicated function for this: MROUND. Example:
    <code>
    =MROUND(A1,0.25)</code>

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rounding to a quarter... (Excel 2000 >)

    Tx...I did not know that....nice reply.
    Regards,
    Rudi

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

    Re: Rounding to a quarter... (Excel 2000 >)

    Hi Rudi,

    For workbooks used by others, it's best not to rely on Analysis ToolPak functionality, since the workbooks won't calculate correctly if the user doesn't have the Analysis ToolPak installed.

    As for your formula, a shorter form is:
    =ROUND(A1*4,0)/4
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rounding to a quarter... (Excel 2000 >)

    Tx for your input. I tried it out and its interesting to note that in your version you need to multiply in the round, and then divide, whereas in the decimaled version, you must divide in the round and then multiply.
    Very interesting...
    Cheers
    Regards,
    Rudi

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

    Re: Rounding to a quarter... (Excel 2000 >)

    In the end, it's all just a matter of reciprocals.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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