Time Formula Help

[SirDracoReaper]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Answered
[/TD]
[TD]Answered after 20secs
[/TD]
[TD]% answered after 20Secs
[/TD]
[TD]Total Answer Delay
[/TD]
[TD]Average Answer Delay
[/TD]
[TD]Max Answer Delay
[/TD]
[/TR]
[TR]
[TD]79
[/TD]
[TD]46
[/TD]
[TD]58.23%
[/TD]
[TD]00:57:18
[/TD]
[TD]00:00:44
[/TD]
[TD]0:03:28
[/TD]
[/TR]
</tbody>[/TABLE]

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]

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

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
callnumber	date	answer time	skilltype

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1-1-2016[/TD]
[TD="align: right"]00:00:15[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1-1-2016[/TD]
[TD="align: right"]00:02:30[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1-1-2016[/TD]
[TD="align: right"]00:00:35[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2-1-2016[/TD]
[TD="align: right"]00:01:23[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2-1-2016[/TD]
[TD="align: right"]00:02:30[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2-1-2016[/TD]
[TD="align: right"]00:00:35[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]3-1-2016[/TD]
[TD="align: right"]00:00:15[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]3-1-2016[/TD]
[TD="align: right"]00:00:15[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]3-1-2016[/TD]
[TD="align: right"]00:01:23[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]3-1-2016[/TD]
[TD="align: right"]00:02:30[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]3-1-2016[/TD]
[TD="align: right"]00:00:35[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]4-1-2016[/TD]
[TD="align: right"]00:00:15[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]4-1-2016[/TD]
[TD="align: right"]00:00:15[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]4-1-2016[/TD]
[TD="align: right"]00:01:23[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]4-1-2016[/TD]
[TD="align: right"]00:02:30[/TD]
[TD="align: right"]2[/TD]

</tbody>

Sheet4​

Then try this as report:

	A	B	C	D	E	F	G	H	I
Total	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
Calls Answered
Calls Answered After 20 secs
Call Answer after 20secs %
Wait Time
Average Wait time
Max Wait time
Skill 1
Calls Answered
Calls Answered After 20 secs
Call Answer after 20secs %
Wait Time
Average Wait time
Max Wait time

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]33[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]40%[/TD]
[TD="align: right"]57%[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]39%[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]04:58[/TD]
[TD="align: right"]05:28[/TD]
[TD="align: right"]04:58[/TD]
[TD="align: right"]04:58[/TD]
[TD="align: right"]04:58[/TD]
[TD="align: right"]03:20[/TD]
[TD="align: right"]04:28[/TD]
[TD="align: right"]33:08[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]01:00[/TD]
[TD="align: right"]00:47[/TD]
[TD="align: right"]01:00[/TD]
[TD="align: right"]01:00[/TD]
[TD="align: right"]01:00[/TD]
[TD="align: right"]01:07[/TD]
[TD="align: right"]01:29[/TD]
[TD="align: right"]01:00[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]02:30[/TD]
[TD="align: right"]02:30[/TD]
[TD="align: right"]02:30[/TD]
[TD="align: right"]02:30[/TD]
[TD="align: right"]02:30[/TD]
[TD="align: right"]02:30[/TD]
[TD="align: right"]02:30[/TD]
[TD="align: right"]02:30[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]33%[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]35%[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]02:13[/TD]
[TD="align: right"]02:28[/TD]
[TD="align: right"]02:13[/TD]
[TD="align: right"]02:13[/TD]
[TD="align: right"]02:13[/TD]
[TD="align: right"]00:50[/TD]
[TD="align: right"]01:58[/TD]
[TD="align: right"]14:08[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]00:44[/TD]
[TD="align: right"]00:37[/TD]
[TD="align: right"]00:44[/TD]
[TD="align: right"]00:44[/TD]
[TD="align: right"]00:44[/TD]
[TD="align: right"]00:25[/TD]
[TD="align: right"]00:59[/TD]
[TD="align: right"]00:42[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]01:23[/TD]
[TD="align: right"]01:23[/TD]
[TD="align: right"]01:23[/TD]
[TD="align: right"]01:23[/TD]
[TD="align: right"]01:23[/TD]
[TD="align: right"]00:35[/TD]
[TD="align: right"]01:23[/TD]
[TD="align: right"]01:23[/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=SUMPRODUCT(--(WEEKDAY(Sheet4!$B$2:$B$34,1)=B$1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=SUMPRODUCT(--(WEEKDAY(Sheet4!$B$2:$B$34,1)=B$1)*(Sheet4!$C$2:$C$34<=TIME(0,0,20)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]=B4/B3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B6[/TH]
[TD="align: left"]=SUMPRODUCT((WEEKDAY(Sheet4!$B$2:$B$34,1)=B$1)*(Sheet4!$C$2:$C$34))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]=B6/B3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B11[/TH]
[TD="align: left"]=SUMPRODUCT(--(WEEKDAY(Sheet4!$B$2:$B$34,1)=B$1)*(Sheet4!$D$2:$D$34=1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B12[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B13[/TH]
[TD="align: left"]=B12/B11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B14[/TH]
[TD="align: left"]=SUMPRODUCT(((WEEKDAY(Sheet4!$B$2:$B$34,1)=B$1)*(Sheet4!$D$2:$D$34=1)*(Sheet4!$C$2:$C$34)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B15[/TH]
[TD="align: left"]=B14/B11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]{=MAX(IF(WEEKDAY(Sheet4!$B$2:$B$34)=B$1,Sheet4!$C$2:$C$34))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B16[/TH]
[TD="align: left"]{=MAX(IF(WEEKDAY(Sheet4!$B$2:$B$34)=B$1,IF(Sheet4!$D$2:$D$34=1,Sheet4!$C$2:$C$34)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]