Results 1 to 7 of 7

Round to nearest quarter...

This is a discussion on Round to nearest quarter... within the Excel Questions forums, part of the Question Forums category; Is there a way to return a "sum=(a1:a5) and also round to nearest quarter? My number format is "0.00" I ...

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Kansas
    Posts
    5

    Default Round to nearest quarter...

    Is there a way to return a "sum=(a1:a5) and also round to nearest quarter? My number format is "0.00"

    I am working with military time. Thanks!

  2. #2
    Board Regular
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    3,459

    Default Re: Round to nearest quarter...

    This can be done by the following formula (lengthy as you need to round each step of the way)

    =ROUNDDOWN(SUM(A1:A5),0)+IF(ROUND(SUM(A1:A5)-ROUNDDOWN(SUM(A1:A5),0),2)<=0.12,0,IF(ROUND(SUM(A1:A5)-ROUNDDOWN(SUM(A1:A5),0),2)<=0.37,0.25,IF(ROUND(SUM(A1:A5)-ROUNDDOWN(SUM(A1:A5),0),2)<=0.63,0.5,IF(ROUND(SUM(A1:A5)-ROUNDDOWN(SUM(A1:A5),0),2)<=0.87,0.75,IF(ROUND(SUM(A1:A5)-ROUNDDOWN(SUM(A1:A5),0),2)>=0.88,1,0)))))


  3. #3
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,174

    Default Re: Round to nearest quarter...

    Welcome to the Board!

    Have you tried =CEILING?

    Although working with TIME you might want to check out www.cpearson.com

    He's got some great suggestions!

    Smitty

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174

    Default Re: Round to nearest quarter...

    Try:

    =MROUND(SUM(A1:A5),0.25)

    You say your woring with military time but your numbers are 0.00
    I'm taking it that you've converted the time to decimal

    Regards
    "Have a good time......all the time"
    Ian Mac

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: Round to nearest quarter...


  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174

    Default Re: Round to nearest quarter...

    Oh yes, the MROUND function is part of the Analysis Toolpak Add-In

    Goto:

    Tools>Add-Ins> Then chack Analysis ToolPak
    OK

    Also if your values are still Time Values use:

    =MROUND(SUM(A1:A5)*24,0.25)

    Regards
    "Have a good time......all the time"
    Ian Mac

  7. #7
    New Member
    Join Date
    Jul 2011
    Posts
    1

    Default Re: Round to nearest quarter...

    =CEILING(A1*8,2)/8

    0.19 0.25
    0.1 0.25
    0.25 0.25
    0.3 0.50
    0.45 0.50
    0.5 0.50
    0.55 0.75
    0.6 0.75
    0.7 0.75
    0.75 0.75
    0.8 1.00
    0.9 1.00
    1 1.00

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com