| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with averaging a time but dates are messing up the result I am trying for.
I am attempting to get a worksheet that averages times but I am running into a few problems. For example I have this setup.... A1 = 05/11/06 11:00 PM B1 = 05/13/06 11:30 PM C1 = 05/15/06 12:00 AM D1 = 05/17/06 12:30 AM E1 = 05/19/06 01:00 AM F1 = =AVERAGE(A1:E1) In F1 I am getting 9:36 AM which I completely understand (it is averageing from 5/11 11PM to 5/19 1AM) but this is not what I am looking for. I want it to just average the time and have the result of F1 show up as 12:00AM. I tried removing the dates but then it was averaging only in a one day cycle. So it seems the dates are the only way I can figure out how to tell it to average from lets say 11:00 at night to 12:30 the next morning. But as you can see I don't want it to average on the date just the time. Thanks Chad -- chadsxe ------------------------------------------------------------------------ chadsxe's Profile: http://www.excelforum.com/member.php...o&userid=35421 View this thread: http://www.excelforum.com/showthread...hreadid=552284 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with averaging a time but dates are messing up the result I am trying for.
Chad,
Array enter (enter using Ctrl-Shift-Enter) =AVERAGE(A1:E1-INT(A1:E1)) Note that the average isn't around midnight, since times are from 0 - Midnight to .5 Noon to 1 the next Midnight HTH, Bernie MS Excel MVP "chadsxe" wrote in message ... I am attempting to get a worksheet that averages times but I am running into a few problems. For example I have this setup.... A1 = 05/11/06 11:00 PM B1 = 05/13/06 11:30 PM C1 = 05/15/06 12:00 AM D1 = 05/17/06 12:30 AM E1 = 05/19/06 01:00 AM F1 = =AVERAGE(A1:E1) In F1 I am getting 9:36 AM which I completely understand (it is averageing from 5/11 11PM to 5/19 1AM) but this is not what I am looking for. I want it to just average the time and have the result of F1 show up as 12:00AM. I tried removing the dates but then it was averaging only in a one day cycle. So it seems the dates are the only way I can figure out how to tell it to average from lets say 11:00 at night to 12:30 the next morning. But as you can see I don't want it to average on the date just the time. Thanks Chad -- chadsxe ------------------------------------------------------------------------ chadsxe's Profile: http://www.excelforum.com/member.php...o&userid=35421 View this thread: http://www.excelforum.com/showthread...hreadid=552284 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with averaging a time but dates are messing up the result I am trying for.
Bernie Deitrick Wrote: Chad, Array enter (enter using Ctrl-Shift-Enter) =AVERAGE(A1:E1-INT(A1:E1)) Note that the average isn't around midnight, since times are from 0 - Midnight to .5 Noon to 1 the next Midnight HTH, Bernie MS Excel MVP I don't mean to sound stupid and bug you but that went way over my head. Can you please try and explain it in a diffrent way. Thanks Chad -- chadsxe ------------------------------------------------------------------------ chadsxe's Profile: http://www.excelforum.com/member.php...o&userid=35421 View this thread: http://www.excelforum.com/showthread...hreadid=552284 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with averaging a time but dates are messing up the result I am trying for.
Chad,
Excel stores dates as integers, with December 31, 1899 as 0, incrementing 1 for every day since then. So June 15, 2006 is actually stored as the number 38883. This allows dates to be subtracted, etc. Anyway, Excel stores times as fractions of a day: take the time in military format, and divide by 24. So 10 AM is 10/24, or .416666666. Date/time values are stored as decimal nubers, so 10 AM on June 15, 2006 is 38883.4166666666 Then Excel formats these to show the date or time however you want. So, if the value June 16, 2006 10:00 AM is in cell A1, using =INT(A1) will return 38883 (just the date), and using =A1-INT(A1) will return 0.4166666666 (just the time) So my formula returns the average of just the times.... Format the cell with my formula as time, and it will give you the average time as a time value.... HTH, Bernie MS Excel MVP "chadsxe" wrote in message ... Bernie Deitrick Wrote: Chad, Array enter (enter using Ctrl-Shift-Enter) =AVERAGE(A1:E1-INT(A1:E1)) Note that the average isn't around midnight, since times are from 0 - Midnight to .5 Noon to 1 the next Midnight HTH, Bernie MS Excel MVP I don't mean to sound stupid and bug you but that went way over my head. Can you please try and explain it in a diffrent way. Thanks Chad -- chadsxe ------------------------------------------------------------------------ chadsxe's Profile: http://www.excelforum.com/member.php...o&userid=35421 View this thread: http://www.excelforum.com/showthread...hreadid=552284 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with averaging a time but dates are messing up the result I am trying for.
O.k. that makes sense and I have tested it out but there is still that one flaw. Say for example A1 = 11:30PM (driver come in at 11:30 at night) A2 = 12:00AM (driver comes in a half hour later at 12:00 in the morning) The formula you suggested looks at this and says this is on the same day. But in reality it is not no the same dam and the average between the 2 should be 11:45PM. The formula you suggested works fine if the drivers come in on the same day. But as soon as some one comes in past the Mid-night mark it then throughs that time back into the same 24 hour block as the rest of them. Did that make sense? -- chadsxe ------------------------------------------------------------------------ chadsxe's Profile: http://www.excelforum.com/member.php...o&userid=35421 View this thread: http://www.excelforum.com/showthread...hreadid=552284 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with averaging a time but dates are messing up the result I am trying for.
Perhaps just a straight average:
=AVERAGE(A1:E1)-INT(AVERAGE(A1:E1)) formatted as time... HTH, Bernie MS Excel MVP "chadsxe" wrote in message ... O.k. that makes sense and I have tested it out but there is still that one flaw. Say for example A1 = 11:30PM (driver come in at 11:30 at night) A2 = 12:00AM (driver comes in a half hour later at 12:00 in the morning) The formula you suggested looks at this and says this is on the same day. But in reality it is not no the same dam and the average between the 2 should be 11:45PM. The formula you suggested works fine if the drivers come in on the same day. But as soon as some one comes in past the Mid-night mark it then throughs that time back into the same 24 hour block as the rest of them. Did that make sense? -- chadsxe ------------------------------------------------------------------------ chadsxe's Profile: http://www.excelforum.com/member.php...o&userid=35421 View this thread: http://www.excelforum.com/showthread...hreadid=552284 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with averaging a time but dates are messing up the result I am trying for.
No luck... That does pretty much the same thing as the other formula. I am starting to think this is not possible. How do you take 06/12/06 11:55 Pm 06/19/06 12:55 Am Average the two times not based on the dates but based on the fact that one is in the morning of a diffrent day. Hmmm....I Don't know  -- chadsxe ------------------------------------------------------------------------ chadsxe's Profile: http://www.excelforum.com/member.php...o&userid=35421 View this thread: http://www.excelforum.com/showthread...hreadid=552284 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
| EXCEL:NUMBER TO GREEK WORDS | Excel Worksheet Functions | |||
| Amount or Numbers in Words | New Users to Excel | |||
| Convert Numeric into Text | Excel Worksheet Functions | |||
| Spellnumber | Excel Worksheet Functions | |||
Linear Mode
