Try one of these:
This is an array formula** :
=AVERAGE(IF(E2:E13="complete",F2:F13))
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
Or, this normally entered formula:
=SUMIF(E2:E13,"complete",F2:F13)/COUNTIF(E2:E13,"complete")
Format either as [h]:mm
--
Biff
Microsoft Excel MVP
Ok, try these:
C2: Format as General or Number
=INT((A2+B2)-(A1+B1))
D2: Format as Custom h:mm
=MOD(B2-B1,1)
E2: Format as Custom [h]:mm (note the [ ] around the hours)
=(A2+B2)-(A1+B1)
--
Biff
Microsoft Excel MVP
Using your original sample data:
Tell us what results you expect and in what cell(s) should the results
appear.
Good point, that looks much clearer.
Everything on my chart is given with the exception of "E"
C is the number of 24 hour periods,
D is the hours beyond that that are less than 24 hours.
for line one, the ticket is opened at time "0".
E is the output I am looking for.
.............A..................B.........C..........D.............E
1...12/17/2008........13:23.......""..........0:00 .........0
2...12/19/2008........15:35.......2...........2:12 ........50:12
Thanks.Crzzy1
----------------------------------
Thank you Biff,
Hey that seems to have fixed it.
=(A2+B2)-(A1+B1) didn't seem to work for me the first time I tried
it, sorry.
:Maybe I did forget the "[" "]" on the [h];mm.
anyways, thanks.
now I have this.. with the correct time on my tickets thanks to you.
(I included a little logic in the F columb, also that includes your
formula)
=IF(E6="Opened",0,IF(E5="Opened",(C6+D6)-(C5+D5),IF(E4="Opened",
(C6+D6)-(C4+D4),IF(E3="Opened",(C6+D6)-(C3+D3),"error"))))
a b c d e f
10006 jfpi 6/21/2005 23:47 Opened 0:00
10006 jfpi 6/22/2005 4:13 Picked 4:26
10006 jfpi 6/22/2005 4:15 Approved 4:28
10006 jfpi 6/23/2005 4:39 Complete 28:52
10009 amek 6/22/2005 8:43 Opened 0:00
10009 amek 6/22/2005 8:44 Picked 0:01
10009 amek 6/22/2005 8:49 Approved 0:06
10009 amek 6/22/2005 8:51 Complete 0:08
10010 carp 6/22/2005 8:51 Opened 0:00
10010 carp 6/22/2005 8:59 Picked 0:08
10010 carp 6/22/2005 9:02 Approved 0:11
10010 carp 6/22/2005 9:32 Complete 0:41
If I may dare,,,,
Is there a way I can show the average time for all tickets complete?
Thanks again.
------------------