Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 21

Thread: Rounding to nearest .25 (times)

  1. #1
    Join Date
    28th May 2003
    Posts
    1
    I need to round numbers to the nearest .25, e.g. 8.39 to 8.50, 4.21 to 4.25, etc)

    Any help would be appreciated.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    EDIT: Correct solution below. This was a brain fault. I think there is a function to do this directly -- I'm blanking. But the following should work : if you number is in A1:
    =round(a1*100,0)/100

    Format the cell to two decimal places.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    BRAIN DEAD (me, not you): Sorry, I was rushing out. Use:
    =round(a1*4,0)/4

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    There is a function to do it directly (Thank you, Derk). It requires the Analysis Tool Pack (add-in) be attached.
    =MROUND(a1,0.25)

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    1st February 2005
    Posts
    11

    Re: Rounding to nearest .25

    This has been asked before but I am not understanding it. Would someone please help me with my assignment for school? My assignment is this:

    How do you round the product of one cell multiplied by another cell to the nearest $.25? She is not looking for the problem to be rounded to 2 decimal places. She is wanting to be able to round to the nearest quarter of a cent.

    For example: 4.92 would be rounded to 5.00 and 4.78 would be rounded to 4.75.

    The answer will also work for rounding time to the nearest quarter of an hour or tenth of an hour, ect.

    Please help me with this problem and give me clear instructions.

    Last edited by ccstudent; February 1st, 2005 at 07:36. Reason: found I CAN use the tool pack

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    Re: Rounding to nearest .25

    The Analysis Toolpak ships with Excel - it is part of standard Excel, but you have to activate the addin.

    see here for instructions

    http://office.microsoft.com/en-us/as...277241033.aspx

    Then you can use the formula

    =MROUND(A1,0.25)

    Assuming A1 contains 4.78, the answer will be 4.75
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

  7. #7
    Join Date
    1st February 2005
    Posts
    11

    Re: Rounding to nearest .25

    That was VERY helpful. Thank you very much! Now I am having trouble rounding up to the nearest quarter hour. Please be patient with me due to the fact I am learning the program and I am blonde Thanks again for all your help.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    Re: Rounding to nearest .25

    Can you attach a small example of what you are trying to do...

    I am learning the program and I am blonde



    There are only stupid answers here... there are no stupid questions...
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

  9. #9
    Join Date
    1st February 2005
    Posts
    11

    Re: Rounding to nearest .25

    You helped me to understand rounding help (whiched worked, THANK GOD) Now I need to be able to apply the formule to rounding up to the nearest quarter hour or tenth of an hour (example: 2:36a.m. to 2:45am and 4:05p.m. to 4:15p.m.).

    My instructor was telling me to take 1/(24*4) and to figure it out. But mind you I am not a huge mathmatical person (in fact I do only basic math) so I am confused for sure.

    Thanks again, Steffanie

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    Re: Rounding to nearest .25

    OK, I can see what he's getting at, but he's being a bit of a vague arse if he's supposed to be teaching you. I mean, you don't know Excel right?

    OK, unless you know Excel, you won't know the peculiar way it stores dates & times.

    Dates are stored numerically, starting with the number 1 (which was 1/1/1900) - on that basis, Excl recognises today as being 38,384 - this is why we can subtract 1 date from another and calculate the elapsed days....

    Time values are similar. Granted that 24 Hours has the value of 1 (in date terms), the following logic should make sense.

    24 Hours can be measured as 1/24

    i.e. if a day (in excel terms) is 1 - then it follows that excel thinks of an hour as 1/24

    OK... hope you're still with me...

    So a quarter of an hour is 1/24/4 That's a 1 divided by 24 divided by 4 - this can also be expressed as 1/(24*4)

    So, let's go back to our problem.

    Let's say A1 has the time 12:26

    In A2 type

    =MROUND(A1,1/(24*4))

    Hey... the answer is 12:30

    Which is what you want.

    If you needed to alter it to round to the nearest 10 minute slot..... well, there are 4*15 minutes in 1 hour & 6*10 minutes...

    so it's

    =MROUND(A1,1/(24*6))


    Hope this helps.... at least more so than "Professor Vague"
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Rounding To Nearest x
    By mcubitt in forum EXCEL HELP
    Replies: 24
    Last Post: November 2nd, 2006, 20:40
  2. Rounding to the nearest 50
    By Poohbeart in forum EXCEL HELP
    Replies: 5
    Last Post: June 22nd, 2006, 02:55
  3. rounding to the nearest .25
    By SUGA287 in forum EXCEL HELP
    Replies: 11
    Last Post: May 19th, 2005, 05:51
  4. Rounding to the nearest $500.
    By teknekjeff in forum EXCEL HELP
    Replies: 2
    Last Post: March 10th, 2005, 23:01
  5. rounding to nearest 9 or 5
    By lilthrift in forum EXCEL HELP
    Replies: 7
    Last Post: January 4th, 2005, 17:03

Bookmarks

Posting Permissions

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