Support Solved How to Calculate Rounding on Microsoft Excel

Discussion in 'Technology' started by Amaury, Mar 1, 2015.

Thread Status:
Not open for further replies.
  1. Amaury Legendary Hero

    Joined:
    Jan 15, 2007
    Gender:
    Male
    Location:
    Ellensburg, WA
    1,692
    So as most of you staff and premiums know, I've been keeping track of my last activity since February 23, 2014 (see the Activity Log thread by going to the Premium Members forum or the Current Forum Projects tab in my signature), and shortly afterward (after the first two weeks, I think) I had @Ienzo start figuring out the averages for me. Before long I had Ienzo show how she did the work and eventually I started doing it on my own, having her check my work for the longest time. Not too long ago, I stopped tagging her after I was confident I had it down and continued finding the averages for each week by myself.

    I've yet to update the thread today because I wanted to change the organization of the thread a bit. While doing some research, I discovered that you can use Microsoft Excel to find the averages of any numbers (e.g., time), and since this is something I do just for fun and not for a class or something, I figured it'd go by quicker if I did it this way on my mom's computer rather than by hand. However, I don't think it's taking rounding into its calculations. For example, these were the times for week three:
    • 2:28 AM
    • 1:52 AM
    • 12:22 AM
    • 12:01 AM
    • 12:38 AM
    • 12:26 AM
    • 12:37 AM

    Excel Average: 12:54 AM

    However, with my work -- or, rather, Ienzo's work since week three was one of the ones she did -- I get an average of 12:55 AM:
    • 28/60 = 0.466 (0.467) + 14 = 14.466 (14.467)
    • 52/60 = 0.866 (0.867) + 13 = 13.866 (13.867)
    • 22/60 = 0.366 (0.367) + 12 = 12.366 (12.367)
    • 01/60 = 0.016 (0.017) + 12 = 12.016 (12.017)
    • 38/60 = 0.633 + 12 = 12.633
    • 26/60 = 0.433 + 12 = 12.433
    • 37/60 = 0.616 (0.617) + 12 = 12.616 (12.617)

    14.466 (14.467) + 13.866 (13.867) + 12.366 (12.367) + 12.016 (12.017) + 12.633 + 12.433 + 12.616 (12.617) = 90.396 (90.401)

    90.396 (90.401) / 7 = 12.9137 (12.9144)

    The hour is 12, so now just find the minutes: 0.9137 (0.9144) * 60 = 54.822 (54.864)

    Round that to the nearest full number, which would be 55 because the number after the decimal is five or higher, which means we round up. Therefore, our average is 12:55 AM.

    You can also round up here (shown in the parentheses -- there are more numbers, but I only include the first three numbers after the decimal for the individual times and the first four numbers after the decimal for the sum of the times, like Ienzo), but I don't worry too much about it there because the answers before the final calculations come out to essentially the same, and the final calculation would still be 12:55 AM, for example.

    Anyway, either Microsoft Excel isn't rounding or it is and the answer by hand above isn't fully accurate. Any ideas? (@Mixt, I know you're a wiz with computer applications; maybe you and Ienzo can help.)

    Edit #1: In case it's of any case use, the formula I used is =AVERAGE(A1:A7).

    Edit #2: Okay, so I got that solved by changing the decimal point from the default two places after to three places after, which made Excel display it as 12:54:51 AM, which would be 12:55 AM rounded. Now, usually you don't round with time, but since this is a project just for fun, it's harmless. If the seconds are 29 or less, leave it the same; if the seconds are 30 or higher, round up. However, while both Excel and I agreed it was 12:54 AM for the set of times above, it doesn't seem to want to agree with me with some sets of times. For example, if I use last week's (2/22 - 2/28) set of times:
    • 12:22 AM
    • 12:21 AM
    • 12:43 AM
    • 11:10 PM
    • 12:16 AM
    • 2:15 AM
    • 10:46 PM

    I get an average of 12:16 AM by hand, which is correct; however, Excel gets an average of 7:07:34 AM (7:08 AM rounded), which I know, even if I didn't do it by hand, cannot be correct, so what am I missing?

    If someone could help, I'd be most grateful!

    Edit #3: From receiving help here, the issue only happens when and is to do with the fact that there's a mix of AM and PM (when it's all either AM or PM, there's not an issue). When there's a mix of AM and PM, I need to use negative numbers for the PMs stating how many hours and minutes the PM time is behind 12:00 AM. For example, 10:46 PM is an hour and 14 minutes behind 12:00 AM, but when I try putting in -1:14 per a post in that thread, I get a circular reference warning and it doesn't work.
     
    Last edited: Mar 2, 2015
  2. Amaury Legendary Hero

    Joined:
    Jan 15, 2007
    Gender:
    Male
    Location:
    Ellensburg, WA
    1,692
    So here are a couple screenshots of my spreadsheet, which shows my formula:

    upload_2015-3-2_17-28-30.png

    upload_2015-3-2_17-29-37.png

    As long as the times are all AM or all PM, I'm good, but I need an additional formula when there's a mix of AM and PM because I'm really finding the average between two different days, and that's still going over my head and I haven't been able to grasp what I've been told.

    I need step-by-step instructions, likely with screenshots highlighting each step.

    Help will really be appreciated!

    Edit: If I try the solution here, which works for that guy, adjusting the numbers as necessary, it doesn't work for me due to an error:

    upload_2015-3-2_19-17-16.png

    Please help me! :(
     
    Last edited: Mar 2, 2015
  3. Mixt The dude that does the thing

    Joined:
    Oct 18, 2006
    Gender:
    Male
    826
    Remember you are using Openoffice Calc, not Microsoft Excel. It uses slightly different rules for formulas. In this case you are getting a 509 error because you need to use semicolons instead of commas. Try =IF(HOUR(G2)>12;G2-1;G2)


    Also, while I'm not familiar with your setup enough as to know why a negative number might cause a circular reference, you can force some of those errors to resolve anyway.
    Go to Tools > Options.
    In the right box expand "OpenOffice Calc" and select "Calculate".
    The first section is "Iterative references" turn that on.

    By enabling this it will calculate until the last two answers are close enough to each other (Minimum Change). If it goes in it's circle a given number of times (Steps) without meeting the minimum change then it will give you a 523 error.
     
  4. Amaury Legendary Hero

    Joined:
    Jan 15, 2007
    Gender:
    Male
    Location:
    Ellensburg, WA
    1,692
    OMG, MIXT! IS IT OKAY IF I LOVE YOU!? I did a test at the top before doing it anywhere else, so I just gotta remember to change the letters in the formula elsewhere.

    upload_2015-3-4_23-13-28.png

    Edit: And yeah, I was going to do it on Microsoft Excel on my mom's computer because I couldn't get OpenOffice Calc to work properly for me at first, but I likely just did something wrong.
     
    Last edited: Mar 5, 2015
Thread Status:
Not open for further replies.