average based on weekday
 

I'm trying to average a column of numbers based on the day of the week.
Col A has the sequential days of the month, Col B has a number for each day.
I want to average all the numbers in Col B that are based on a weekday or
weekend.
--
Thanks,
Nick

Nick,

For the weekend average,

=SUMPRODUCT((WEEKDAY(A1:A365,2)5)*B1:B365)/SUMPRODUCT((WEEKDAY(A1:A365,2)5)*1)

For the weekday average
=SUMPRODUCT((WEEKDAY(A1:A365,2)<6)*B1:B365)/SUMPRODUCT((WEEKDAY(A1:A365,2)<6)*1)

HTH,
Bernie
MS Excel MVP


"Nick" wrote in message
...
I'm trying to average a column of numbers based on the day of the week.
Col A has the sequential days of the month, Col B has a number for each day.
I want to average all the numbers in Col B that are based on a weekday or
weekend.
--
Thanks,
Nick

Weekends: =SUMPRODUCT(--(WEEKDAY(A2:A10,2)5),B2:B10)
Weekdays: =SUMPRODUCT(--(WEEKDAY(A2:A10,2)<=5),B2:B10)

--
HTH

Bob Phillips

"Nick" wrote in message
...
I'm trying to average a column of numbers based on the day of the week.
Col A has the sequential days of the month, Col B has a number for each
day.
I want to average all the numbers in Col B that are based on a weekday or
weekend.
--
Thanks,
Nick

Try, array-entered, i.e. press CTRL+SHIFT+ENTER:

For weekdays:
=AVERAGE(IF(WEEKDAY(A1:A8,2)={1,2,3,4,5},B1:B8))

For weekends:
=AVERAGE(IF(WEEKDAY(A1:A8,2)={6,7},B1:B8))

Adapt the ranges to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Nick" wrote in message
...
I'm trying to average a column of numbers based on the day of the week.
Col A has the sequential days of the month, Col B has a number for each
day.
I want to average all the numbers in Col B that are based on a weekday or
weekend.
--
Thanks,
Nick

WOW, three great answers with three different approachs and in such a short
time.

Thank you ever so much,
Nick


"Max" wrote:

Try, array-entered, i.e. press CTRL+SHIFT+ENTER:

For weekdays:
=AVERAGE(IF(WEEKDAY(A1:A8,2)={1,2,3,4,5},B1:B8))

For weekends:
=AVERAGE(IF(WEEKDAY(A1:A8,2)={6,7},B1:B8))

Adapt the ranges to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Nick" wrote in message
...
I'm trying to average a column of numbers based on the day of the week.
Col A has the sequential days of the month, Col B has a number for each
day.
I want to average all the numbers in Col B that are based on a weekday or
weekend.
--
Thanks,
Nick

I like this answer best Max, but haven't we been here before

=AVERAGE(IF(WEEKDAY(A1:A8,2)<=5,B1:B8))

<EVBG

Bob

"Max" wrote in message
...
Try, array-entered, i.e. press CTRL+SHIFT+ENTER:

For weekdays:
=AVERAGE(IF(WEEKDAY(A1:A8,2)={1,2,3,4,5},B1:B8))

For weekends:
=AVERAGE(IF(WEEKDAY(A1:A8,2)={6,7},B1:B8))

Adapt the ranges to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Nick" wrote in message
...
I'm trying to average a column of numbers based on the day of the week.
Col A has the sequential days of the month, Col B has a number for each
day.
I want to average all the numbers in Col B that are based on a weekday
or
weekend.
--
Thanks,
Nick

You're welcome !
Thanks for the feedback (from us <g) ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Nick" wrote in message
...
WOW, three great answers with three different approachs
and in such a short time.

Thank you ever so much,
Nick

"Bob Phillips" wrote
I like this answer best Max, but haven't we been here before
=AVERAGE(IF(WEEKDAY(A1:A8,2)<=5,B1:B8))
<EVBG

Yes (scratch-scratch) I vaguely recollect .. the conditional formatting
episode ? <bg. Heck, it's now confirmed as one of my several blind spots:
that 1 week = 7 days only, Bob ! Cheers.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

hey Max,

Greeting from Wessex

The best part is that you used the ,2 argument to make the list simpler, but
stopped one step short

=AVERAGE(IF(WEEKDAY(A1:A8,2)={1,2,3,4,5},B1:B8))

which I think is as previous.

Be content in that the concept was the best though <g

Bob

"Max" wrote in message
...
"Bob Phillips" wrote
I like this answer best Max, but haven't we been here before
=AVERAGE(IF(WEEKDAY(A1:A8,2)<=5,B1:B8))
<EVBG

Yes (scratch-scratch) I vaguely recollect .. the conditional formatting
episode ? <bg. Heck, it's now confirmed as one of my several blind
spots:
that 1 week = 7 days only, Bob ! Cheers.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Be content in that the concept was the best though <g

Thanks for the view ! <g
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----