Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2010
    Location
    Quincy, MA
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I find I don't very well understand the use of time in conjunction with the AVERAGE formula and with conditional formatting. With a delivery deadline of 2:00 AM, I use a conditional format to alert me if I exceed the deadline, while I also track the average delivery time.
    2:40 AM12:10 AM12:13 AM12:08 AM11:59 PM11:59 PM12:15 AM
    The average on this data turns out to be 10:46 AM which is really not the case. Also, if I say the value for Conditional Formatting is > 2:00, the two 23:59 values pop out in RED; to avoid that false positive if I choose the value > 1/1/1900 2:00, I have to remember to input anything actually greater than 2:00 AM as 1/1/1900 X:XX or in the example, 1/1/1900 2:40. Is there a more efficient way of configuring the Conditional Formatting while at the same time getting a better average? I'm using Excel 2003 (11.8328.8329) SP3. Thanks

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Let's tackle the average issue first.

    The average of the times you quote is 12:34PM, which is what Excel gives me using the AVERAGE function when I use the 24 hour clock system. I have attached the file: the format of the cells in question is "Time" so that you can see that the times are correctly entered.

    However if you make all the times "AM" then the average is 10:34 which is similar to what you are experiencing - you cannot be entering the morning and afternoon times correctly? Can you post a sample spreadsheet showing how you are entering the data ?
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Jun 2010
    Location
    Quincy, MA
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you for your response. One of the things I did notice is that your times reflected PM when all times should be AM (except for the two at 11:59 / 23:59). I have included a sample file as you requested, please note that Columns A & C have two different Conditional Formatting Rules. A = 2:00 while C = 1/1/1900 2:00. The Delivery time is 2:00 AM or 2:00 not 2:00 PM or 14:00. Again, thank you for your response. [attachment=90880:WOPR.xls]
    Attached Files Attached Files

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I do find a 12 hour clock prone to confusion (mine) and always use a 24 hour clock for calculations.

    Anyway, the reason your columns A and C give a different average is that in column C the first time (02:40) is entered in the cell as a Date/Time (1 January 1900, 02:40). You cannot get a meaningful average from mixed dates and times.

    As for the conditional formatting, I am not clear what it is that you want. Please can you give a clear description of the conditions when you want the cell to be highlighted in red ? When you say "> 2:00" did you mean "later than 02:00" (later than 2AM) ? 23:59 is indeed later than 02:00 which is why those cells are red. If you meant something else, please say what.

    Thanks.

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    London, UK
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Excel stores dates and times as numbers representing days from 00:00 on Jan 1 1900. Therefore, (to 5 dp)
    12:00 AM = 0.00000
    12:00 PM = 0.50000
    11:59 PM = 0.99931
    02:40 AM = 0.11111
    12:10 AM = 0.00694
    etc.

    Quote Originally Posted by Pete Sweeney View Post
    2:40 AM 12:10 AM 12:13 AM 12:08 AM 11:59 PM 11:59 PM 12:15 AM
    Quote Originally Posted by MartinM View Post
    The average of the times you quote is 12:34PM, ... if you make all the times "AM" then the average is 10:34
    The average of the equivalent values of those times, according to my calculations, is 0.30595, which equates to 7:20 AM, which is what Excel comes back with too. MartinM, I'm not sure where you got 12:34 PM from, but you appear to be right about Pete converting them all to AM (although I get 10:46 AM which is what Pete got).

    Pete, I think where you're running into trouble is (1) AM/PM needs to be explicitly specified in the time, and (2) times in Excel are treated as if they are on the same day, i.e. 12:00 AM - 11:59 PM so 11:59 PM is always later than 2:00 AM. If you want to process times on different days you will have to specify the date too.

    What I would suggest in your case, however, is convert those times into the differences from 2:00 AM which, from your post, I understand is your target time. That way you can calculate averages without tracking dates too.

    You will need to convert the times into numbers since Excel cannot format negative numbers as times or dates, and then treat 11:59 PM as a negative number, telling Excel to treat it as the previous day by subtracting 1 (to get -0.00069 to 5dp). Subtract those values from 2:00 AM (0.08333) and you have the difference in days.

    You should then be able to calculate the average from those values, and convert that back to a time by adding 2:00 AM and applying an appropriate number format. Remember to add 1 if you get a negative number!

Posting Permissions

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