Trying to find Average AHT

[Muhammad Uzair Uddin]

Hello, I want to find Average AHT in Cell H8 and using the formula =AVERAGEIFS('Raw Data'!H:H,'Raw Data'!C:C,Table2[[#This Row],[Supervisor]]) and got wrong value like H8=11573%

Sheet 2
[TABLE="width: 718"]
<tbody>[TR]
[TD]6
[/TD]
[TD]A
[/TD]
[TD] E
[/TD]
[TD] F
[/TD]
[TD] G
[/TD]
[TD] H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Supervisor
[/TD]
[TD]Count of Agents
[/TD]
[TD]Total Calls
[/TD]
[TD]Average Quality
[/TD]
[TD]Average AHT
[/TD]
[TD]Count of Agents (Utilization less then 95 %)
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]SUP A
[/TD]
[TD]17
[/TD]
[TD]72281
[/TD]
[TD] 90%
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]SUP B
[/TD]
[TD]17
[/TD]
[TD]66309
[/TD]
[TD] 88%
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]SUP C
[/TD]
[TD]16
[/TD]
[TD]63286
[/TD]
[TD] 87%
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]SUP D
[/TD]
[TD]17
[/TD]
[TD]64984
[/TD]
[TD] 89%
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]SUP E
[/TD]
[TD]17
[/TD]
[TD]65738
[/TD]
[TD] 87%
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]SUP F
[/TD]
[TD]16
[/TD]
[TD]60327
[/TD]
[TD] 86%
[/TD]
[TD]
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]SUP G
[/TD]
[TD]17
[/TD]
[TD]65677
[/TD]
[TD] 88%
[/TD]
[TD]
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]SUP H
[/TD]
[TD]17
[/TD]
[TD]64862
[/TD]
[TD] 88%
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

Sharing some Raw data from sheet 1 which we will use for result
[TABLE="width: 289"]
<tbody>[TR]
[TD]
[TABLE="width: 604"]
<tbody>[TR]
[TD]
[/TD]
[TD] A
[/TD]
[TD] B
[/TD]
[TD] C
[/TD]
[TD] D
[/TD]
[TD] E
[/TD]
[TD] F
[/TD]
[TD] G
[/TD]
[TD] H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Prepaid
[/TD]
[TD]Agent CODE
[/TD]
[TD]Supervisor
[/TD]
[TD]Hold Time
[/TD]
[TD]Utilization
[/TD]
[TD]Total Calls
[/TD]
[TD]Aux
[/TD]
[TD] AHT
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]3280332
[/TD]
[TD] 12
[/TD]
[TD]Sup A
[/TD]
[TD]1:28:54
[/TD]
[TD] 100.95%
[/TD]
[TD]5088
[/TD]
[TD] 0
[/TD]
[TD]106.99
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]3280433
[/TD]
[TD] 13
[/TD]
[TD]Sup B
[/TD]
[TD]1:46:27
[/TD]
[TD] 102.22%
[/TD]
[TD]4885
[/TD]
[TD] 1
[/TD]
[TD]120.76
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]380859
[/TD]
[TD] 14
[/TD]
[TD]Sup C
[/TD]
[TD]2:46:32
[/TD]
[TD] 98.24%
[/TD]
[TD]4066
[/TD]
[TD] 1
[/TD]
[TD]108.65
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]3280403
[/TD]
[TD] 45
[/TD]
[TD]Sup D
[/TD]
[TD]8:22:43
[/TD]
[TD] 100.92%
[/TD]
[TD]4676
[/TD]
[TD] 0
[/TD]
[TD]110.37
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]340651
[/TD]
[TD] 455
[/TD]
[TD]Sup E
[/TD]
[TD]3:42:49
[/TD]
[TD] 98.91%
[/TD]
[TD]2332
[/TD]
[TD] 2
[/TD]
[TD]113.50
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]3280383
[/TD]
[TD] 1
[/TD]
[TD]Sup A
[/TD]
[TD]1:33:02
[/TD]
[TD] 100.98%
[/TD]
[TD]4644
[/TD]
[TD] 4
[/TD]
[TD]118.02
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]340989
[/TD]
[TD] 5
[/TD]
[TD]Sup B
[/TD]
[TD]0:29:48
[/TD]
[TD] 93.26%
[/TD]
[TD]3546
[/TD]
[TD] 1
[/TD]
[TD]118.01
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]340995
[/TD]
[TD] 55
[/TD]
[TD]Sup F
[/TD]
[TD]1:57:45
[/TD]
[TD] 94.61%
[/TD]
[TD]3879
[/TD]
[TD] 0
[/TD]
[TD]116.73
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]340429
[/TD]
[TD] 64
[/TD]
[TD]Sup B
[/TD]
[TD]2:29:24
[/TD]
[TD] 98.27%
[/TD]
[TD]4116
[/TD]
[TD] 1
[/TD]
[TD]113.85
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]380595
[/TD]
[TD] 72
[/TD]
[TD]Sup G
[/TD]
[TD]0:57:42
[/TD]
[TD] 98.24%
[/TD]
[TD]1998
[/TD]
[TD] 0
[/TD]
[TD]125.10
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]3280005
[/TD]
[TD] 466
[/TD]
[TD]Sup H
[/TD]
[TD]3:22:23
[/TD]
[TD] 99.33%
[/TD]
[TD]3620
[/TD]
[TD] 0
[/TD]
[TD]114.68
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 710"]
<colgroup><col><col><col span="2"><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Can anyone help so id be grateful for any help

 

[RonB1111]

Enter this in H8 of Sheet2 and copy down:

=AVERAGEIFS('Raw Data'!H:H,'Raw Data'!C:C,A8)

 

[Muhammad Uzair Uddin]

Dear,
unfortunately after putting the formula =AVERAGEIFS('Raw Data'!H:H,'Raw Data'!C:C,A8) we got wrong value that H8=11573% value should be in 100% or below.

 

[RonB1111]

It works properly for me with the data you supplied. Here's a link to my excel file so you can see it:
https://drive.google.com/file/d/0B1mqGQ8FC-rCUEtLMHF6ZUFveHc/edit?usp=sharing

If that doesn't help, please upload your file to a file sharing service. Be sure to set it so anyone with the link can open it without a password. If you're unable to upload a file, please explain how the "Average" of numbers can be a percentage - perhaps show what result you're expecting and the math on how you get that result.

 

[Muhammad Uzair Uddin]

Yes Got it! Thank you so much RonB1111. It was my mistake that data types of cell was in percentage but now I have converted into general and found correct result thorough your formula

 

[RonB1111]

You're welcome!