Averaging Month & weekdays

[El Bee]

I have a spreadsheet that has 4 columns (month name, date, calls offered, &
calls handled.)
I have sorted the file by month, date and now I need to get the averages (by
day of week) for each of the last 2 columns.
In th sample below there are 2 Wed. and 2 Thurs. that I need to average.
I need to place this formula(s) in a different colmn to the right of the data.
I'm not sure if a formula will work. I have attempted a pivot table but I
am new to these and it gets really ugly everytime I've attempted to create
one.
There is more data; entire years worth.

I am at a loss on how to approach this challenge and any help would be
appreciated. I've tried search this site but my search keys aren't working.

Thanks for your time and efforts.

Sample data:

Month DOW Date Offered Handled
January Tue 01/01/08 222 209
January Wed 01/02/08 447 412
January Wed 01/02/08 149 521
January Thu 01/03/08 450 398
January Thu 01/03/08 432 405
January Fri 01/04/08 362 328
January Sat 01/05/08 369 321

 

[Mike H]

Hi,

This does that for column D. Put it in a cell and enter as an array. see
instructions below, and drag down for 6 cells for each day of the week.

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array.

It will return div/0 error if there are no data for that day of week but you
can wrap it in an iserror to eliminate that.

=AVERAGE(IF(WEEKDAY($C$1:$C$50)=ROW(A1),$D$1:$D$50))

you should

 

[Suleman Peerzade]

Hi,

I suggest you try with subtotals.

click on the header row go to the menubar and DATA|Subtotals
in the subtotals select 1. (at each change in) Date, 2. (Use
function)Average, 3.(Add subtotals to) offered and handled.

Please uncheck replace the current subtotals

This is working fine as per your requirement. the example is given below.

Month DOW Date Offered Handled
January Tue 1/1/08 222 209
1/1/08 Average 222 209
January Wed 1/2/08 447 412
January Wed 1/2/08 149 521
1/2/08 Average 298 466.5
January Thu 1/3/08 450 398
January Thu 1/3/08 432 405
1/3/08 Average 441 401.5
January Fri 1/4/08 362 328
1/4/08 Average 362 328
January Sat 1/5/08 369 321
1/5/08 Average 369 321
Grand Average 347.2857143 370.5714286

 

[El Bee]

Mike,

I attempted your formula and it didn't work
It came back with #VALUE
Here's the modified formula:
=AVERAGE(IF(WEEKDAY($C$2:$C$8)=ROW(A2),$D$1:$D$8))

A B C D E
1 Month DOW Date Offered Handled
2 January Tue 1/1/2008 222 209
3 January Wed 1/2/2008 447 412
4 January Wed 1/2/2008 149 521
5 January Thu 1/3/2008 450 398
6 January Thu 1/3/2008 432 405
7 January Fri 1/4/2008 362 328
8 January Sat 1/5/2008 369 321

 

[El Bee]

Suleman,
I need to gather these subtotals and make a graph for projecting future
needs. Can you create a graph from subtotal rows without having to
copy/paste those values somewhere else on the spreadsheet?

Thanks for replying