Time Formula Help

[SirDracoReaper]

Answered	Answered after 20secs	% answered after 20Secs	Total Answer Delay	Average Answer Delay	Max Answer Delay
79	46	58.23%	00:57:18	00:00:44	0:03:28

<tbody>
</tbody>

So i have a report that comes of daily and we then have to put this onto an excel sheet but it only gives a break down by skillset not an overall so need to work out the formula.

So the answered and answered after we type onto sheet.
The % is the answered after divided by the answered.
The total Answer Delay is typed in.
But how do we work out the Average Answer delay and the max answer delay??

I am using microsoft office 2003.

 

[jorismoerings]

Hi,

To answer this it would be useful if you share a sample of the way your data file is shaped.

 

[SirDracoReaper]

Total	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
Calls Answered	=sum(of calls answered in this collum)	=sum(of calls answered in this collum)	=sum(of calls answered in this collum)	=sum(of calls answered in this collum)	=sum(of calls answered in this collum)	=sum(of calls answered in this collum)	=sum(of calls answered in this collum)	=sum(of this row)
Calls Answered After 20 secs	=sum(of calls answered after 20secs in this collum)	=sum(of calls answered after 20secs in this collum)	=sum(of calls answered after 20secs in this collum)	=sum(of calls answered after 20secs in this collum)	=sum(of calls answered after 20secs in this collum)	=sum(of calls answered after 20secs in this collum)	=sum(of calls answered after 20secs in this collum)	=sum(of this row)
Call Answer after 20secs %	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered
Wait Time	=sum(of wait time in this collum)	=sum(of wait time in this collum)	=sum(of wait time in this collum)	=sum(of wait time in this collum)	=sum(of wait time in this collum)	=sum(of wait time in this collum)	=sum(of wait time in this collum)	=sum(of wait time in this collum)
Average Wait time
Max Wait time
Skill 1
Calls Answered	79	79	79	79	79	79	79	=sum(of this row)
Calls Answered After 20 secs	46	46	46	46	46	46	46	=sum(of this row)
Call Answer after 20secs %	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered
Wait Time	00:57:18	00:57:18	00:57:18	00:57:18	00:57:18	00:57:18	00:57:18	=sum(of this row)
Average Wait time	00:00:44	00:00:44	00:00:44	00:00:44	00:00:44	00:00:44	00:00:44
Max Wait time	00:03:28	00:03:28	00:03:28	00:03:28	00:03:28	00:03:28	00:03:28
Skill 2
Calls Answered	79	79	79	79	79	79	79	=sum(of this row)
Calls Answered After 20 secs	46	46	46	46	46	46	46	=sum(of this row)
Call Answer after 20secs %	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered	=answered after 20 secs divided by answered
Wait Time	00:57:18	00:57:18	00:57:18	00:57:18	00:57:18	00:57:18	00:57:18	=sum(of this row)
Average Wait time	00:00:44	00:00:44	00:00:44	00:00:44	00:00:44	00:00:44	00:00:44
Max Wait time	00:03:28	00:03:28	00:03:28	00:03:28	00:03:28	00:03:28	00:03:28

<tbody>
</tbody>

Here is a rough template of how it would look in excel 2003.
but would like the individual day and overall day to work out its own Average wait and max wait instead of having to type it in.
and then for the all figure which has total of everything to work out the average wait for each day as well as overall week and to work out the max wait for each day as well as the week.

Hope this helps.

 

[jorismoerings]

Hi,

I assumed your data is shaped like this:

	A	B	C	D
1	callnumber	date	answer time	skilltype
2	1	1-1-2016	00:00:15	1
3	4	1-1-2016	00:02:30	2
4	5	1-1-2016	00:00:35	1
5	8	2-1-2016	00:01:23	1
6	9	2-1-2016	00:02:30	2
7	10	2-1-2016	00:00:35	1
8	11	3-1-2016	00:00:15	1
9	12	3-1-2016	00:00:15	2
10	13	3-1-2016	00:01:23	1
11	14	3-1-2016	00:02:30	2
12	15	3-1-2016	00:00:35	1
13	16	4-1-2016	00:00:15	1
14	17	4-1-2016	00:00:15	2
15	18	4-1-2016	00:01:23	1
16	19	4-1-2016	00:02:30	2

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Then try this as report:

	A	B	C	D	E	F	G	H	I
1		1	2	3	4	5	6	7
2	Total	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
3	Calls Answered	5	7	5	5	5	3	3	33
4	Calls Answered After 20 secs	2	4	2	2	2	1	0	13
5	Call Answer after 20secs %	40%	57%	40%	40%	40%	33%	0%	39%
6	Wait Time	04:58	05:28	04:58	04:58	04:58	03:20	04:28	33:08
7	Average Wait time	01:00	00:47	01:00	01:00	01:00	01:07	01:29	01:00
8	Max Wait time	02:30	02:30	02:30	02:30	02:30	02:30	02:30	02:30
9
10	Skill 1
11	Calls Answered	3	4	3	3	3	2	2	20
12	Calls Answered After 20 secs	1	2	1	1	1	1	0	7
13	Call Answer after 20secs %	33%	50%	33%	33%	33%	50%	0%	35%
14	Wait Time	02:13	02:28	02:13	02:13	02:13	00:50	01:58	14:08
15	Average Wait time	00:44	00:37	00:44	00:44	00:44	00:25	00:59	00:42
16	Max Wait time	01:23	01:23	01:23	01:23	01:23	00:35	01:23	01:23

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula B3 =SUMPRODUCT(--(WEEKDAY(Sheet4!$B$2:$B$34,1)=B$1)) B4 =SUMPRODUCT(--(WEEKDAY(Sheet4!$B$2:$B$34,1)=B$1)*(Sheet4!$C$2:$C$34<=TIME(0,0,20))) B5 =B4/B3 B6 =SUMPRODUCT((WEEKDAY(Sheet4!$B$2:$B$34,1)=B$1)*(Sheet4!$C$2:$C$34)) B7 =B6/B3 B11 =SUMPRODUCT(--(WEEKDAY(Sheet4!$B$2:$B$34,1)=B$1)*(Sheet4!$D$2:$D$34=1)) B12 =SUMPRODUCT(--(WEEKDAY(Sheet4!$B$2:$B$34,1)=B$1)*(Sheet4!$C$2:$C$34<=TIME(0,0,20))*(Sheet4!$D$2:$D$34=1)) B13 =B12/B11 B14 =SUMPRODUCT(((WEEKDAY(Sheet4!$B$2:$B$34,1)=B$1)*(Sheet4!$D$2:$D$34=1)*(Sheet4!$C$2:$C$34))) B15 =B14/B11 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B8 {=MAX(IF(WEEKDAY(Sheet4!$B$2:$B$34)=B$1,Sheet4!$C$2:$C$34))} B16 {=MAX(IF(WEEKDAY(Sheet4!$B$2:$B$34)=B$1,IF(Sheet4!$D$2:$D$34=1,Sheet4!$C$2:$C$34)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>