How can I find the average of a count of the number of calls per day?

[dw123000]

Hi all

I was wondering if someone could give me some help. I have to find the average number of call per day and per hour.

My Data looks like follows ( small sample - there are roughly 700 calls per day). I also have the hour the call was taken. I am using Excel 2010. I was wondering if I could use powepivot or VBA to achieve this task?

Any help would be gratefully received.

Date	Call Number	Weekday
01/01/2014	275670	Wed
25/01/2014	276720	Sat
07/01/2014	279821	Tue
04/01/2014	278362	Sat
11/01/2014	277745	Sat
17/01/2014	279412	Fri
23/01/2014	265566	Thu
27/01/2014	254014	Mon
25/01/2014	253053	Sat
20/01/2014	250233	Mon
05/01/2014	266707	Sun
29/01/2014	251576	Wed
​

<colgroup><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

If I'm understanding your question, you could do that with worksheet formulas. The specific formulas would depend on whether you are counting days and hours on a 24/7 Operation or less than 7 days per week/ 24 hours per day...

Excel 2013
	E	F	G
1		24/7 Operation	Business Hrs Only (8/5)
2	Total Calls	12	12
3	Total Days	29	21
4	Avg Calls Per Day	0.41	0.57
5	Avg Calls Per Hour	0.02	0.07
Sheet1

Cell Formulas
Range	Formula
F2	=COUNTA($B$2:$B$100000)
F3	=MAX($A$2:$A$100000)-MIN($A$2:$A$100000)+1
F4	=F2/F3
F5	=F4/24
G2	=COUNTA($B$2:$B$100000)
G3	=NETWORKDAYS(MIN($A$2:$A$100000),MAX($A$2:$A$100000))
G4	=G2/G3
G5	=G4/8

These formulas assume you would be analyzing full days of data. The formulas would be different if, for example, you wanted to know average calls per hour for data that spans 6 PM Monday to 10 AM Tuesday.

 

[dw123000]

Many thanks for your speedy response. Apologies - on reflection, I dont think I stated my requirements particularly clearly! I have a years data which I would like to analyse. I was hoping to get the results to look something as follows :

Day of week Average No of calls
Mon 600.45
Tue 587.34
Wed 903.87
Thu 507.43
Fri 703.19
Sat 803.82
Sun 818.72


Hourly averages to look as something like follows:

Hourly Average
0 - 1 25.23
1 - 2 24.12
2 - 3 25.13

....

22 - 23 19.13
23 - 00 18.07

I created a pivot table to count the number of calls per day and per hour as I have to filter by an age range as well ( Mon age 0-4, Mon age 5 - 9 .. etc), but was not able to work out how to apply an average.

Once again - many thanks for your speedy response.

 

[Jerry Sullivan]

I created a pivot table to count the number of calls per day and per hour as I have to filter by an age range as well ( Mon age 0-4, Mon age 5 - 9 .. etc), but was not able to work out how to apply an average.

Could you clarify your need to filter by age ranges? The desired results you show in your last post don't show age ranges.
Are you just applying for a single age range, or needing to get the desired results you show for multiple age ranges (combined with day of week, hour of day).

I'd probably suggest approach the problem differently depending on which of those is needed.