Excel 2007 : Average of calls handled per hour

**djsouljah** · 10-02-2011, 01:34 PM

Hi I have a little question,

I work in a call center where the system calculate the average numbers of calls per hour that an agent takes.

We already have the daily average but we do not have the average of the week before the end of the week. I would like to calculate this on a daily basis instead.

What I have is the agent ID + the average of the number of calls he's taken per hour.

I will be pasting the information in a sheet on a daily basis...

On another sheet I have the Agent ID and on the next column I would like to calculate the Average of the week.

With which formula can I do this ?

**scottylad2** · 10-02-2011, 02:04 PM

can you oad a sample? it looks like a fairly straightfooward =Averageif(Range,AgentID)

**djsouljah** · 10-02-2011, 07:26 PM

Here you go...

I changed the file a bit ..In this case, the formula is supposed to match the Aspect ID and Date of the day with the aspect ID + Date + the Calls per hour (Inc/h) from the Montreal Sheet.. but the formulas giving me N/A for some reason...for some it would be normal cuz their absent that day but for the rest i dunno why its not working, im kinda new with the INDEX and MATCH Functions so I'm clearly doing something wrong here.... can you take a look please...I attached the file.

=INDEX(Montreal!$J$3:$J$611,MATCH(Sheet1!A5&Sheet1!D4,INDEX(Montreal!$C$3:$C$611&Montreal!$B$3:$B$611,,),0))

**scottylad2** · 10-02-2011, 08:59 PM

You have Excel 2007, try this formula. Hope I understood what you wanted, some of your cells even though they have no time have a 0.00 in them so if included a criteria within the Averagifs function to show that as blank in the calculations as that will effect your overall average. If you need that to be included(0.00) then remove the part of the formula that shaows "<>"&0

hope it helps

**djsouljah** · 10-02-2011, 10:33 PM

Thank you friend! Great work! This will do just fine!

I would like to understand this formula though if you don't mind explaining..if it's not too much to ask.

=IFERROR(AVERAGEIFS(Montreal!$J$3:$J$611,Montreal!$J$3:$J$611,"<>"&0,Montreal!$C$3:$C$611,$A6,Montreal!$B$3:$B$611,D$4),"")

**scottylad2** · 10-03-2011, 05:59 AM

Some of your data is either 0 or blank so the resultant Average for those rows will either throw up a Div/o error, which the IFERROR part of the formula eliminates. and if you have 0 values in your formula your Average will be incorrect. Example =Average(2,4) the result will be 3 which is correct but =Average(2,4,0) the result will be 2 which is incorrect, so the part that says "<>"&0 means only figures that are not a 0 will be included in the Average.

The other 2 elements are your initial criteria, Average only those items that match the ID number and the date.

Hope that helps, doing this from my phone so there may well be errors!!

**scottylad2** · 10-03-2011, 06:33 AM

Another thing, IFERROR AND AVERAGEIFS are only available in Excel 2007 or later, if your going to need to use this on earlier versions theres a workaround