Average Function

D

DaS

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!
 
J

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.
 
D

DaS

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
 
J

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
 
D

DaS

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?
 
J

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).
 
D

DaS

Great! Thank you very much for your help!

JW said:
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).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top