Average Function

[Guest]

In column B I have the dates listed from 01/01/07 through 10/31/2007. In
column E I have totals for each day. What I would like to do is have an
average for each day of the week, i.e., Mondays from 01/01/2007-10/31/2007 =
219, Tuesdays from 01/01/2007-10/31/2007= 250 and so on. I know that I will
have to have a separate cell doing it's own calculation for each weekday, I'm
just not that savvy working with the Sumifs, countifs, or day functions.
Thanks!

 

[JW]

One way (Array entered, Ctrl+Shift+Enter)
=AVERAGE(IF(WEEKDAY(B2:B25,1)=2,E2:E25))

The above formula will calculate the average of E2:E25 where the
weekday in B2:B25 is equal to Monday. To do Tuesday, Wednesday, etc,
simply change the WEEKDAY(B2:B25,1)=2 portion to whatever day of the
week you are after.

 

[Guest]

So to change the date to do Tuesday I need to change the 1 or the 2? so
Tuesday will be ...(WEEKDAY(B2:B25,2)=2,...?

Is that right? I've been messing around with it and now I get the #NUM!
statement.

Thanks JW

 

[JW]

All Ctrl+Shift+Enter entered.
Monday:
=AVERAGE(IF(WEEKDAY(B2:B25,1)=2,E2:E25))
Tuesday:
=AVERAGE(IF(WEEKDAY(B2:B25,1)=3,E2:E25))
Wednesday:
=AVERAGE(IF(WEEKDAY(B2:B25,1)=4,E2:E25))
etc
etc

 

[Guest]

After messing around with it, I got it. Thank you very much for your help.
For the sake of knowledge, what does the ....,1) represent?

 

[JW]

The 1 is an optional argument for the return type.
Taken from Excel help:
1 or omitted = Numbers 1 (Sunday) through 7 (Saturday). Behaves like
previous versions of Microsoft Excel.
2 = Numbers 1 (Monday) through 7 (Sunday).
3 = Numbers 0 (Monday) through 6 (Sunday).

 

[Guest]

Great! Thank you very much for your help!

The 1 is an optional argument for the return type.
Taken from Excel help:
1 or omitted = Numbers 1 (Sunday) through 7 (Saturday). Behaves like
previous versions of Microsoft Excel.
2 = Numbers 1 (Monday) through 7 (Sunday).
3 = Numbers 0 (Monday) through 6 (Sunday).