> 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 <at>yahoo<dot>com
----
> 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 <at>yahoo<dot>com
----
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
----
"Nick" <[email protected]> wrote in message
news:[email protected]...
> 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
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" <[email protected]> wrote in message
news:%[email protected]...
> "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 <at>yahoo<dot>com
> ----
>
>
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 <at>yahoo<dot>com
> ----
> "Nick" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
> ----
> "Nick" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <at>yahoo<dot>com
----
"Nick" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
----
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" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
> ----
> "Nick" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
----
"Nick" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
> ----
> "Nick" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <at>yahoo<dot>com
----
"Nick" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
----
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" <[email protected]> wrote in message
news:%[email protected]...
> "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 <at>yahoo<dot>com
> ----
>
>
> 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 <at>yahoo<dot>com
----
"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 <at>yahoo<dot>com
----
> 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 <at>yahoo<dot>com
----
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" <[email protected]> wrote in message
news:%[email protected]...
> "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 <at>yahoo<dot>com
> ----
>
>
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 <at>yahoo<dot>com
----
"Nick" <[email protected]> wrote in message
news:[email protected]...
> WOW, three great answers with three different approachs
> and in such a short time.
>
> Thank you ever so much,
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
> ----
> "Nick" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <at>yahoo<dot>com
> ----
> "Nick" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <at>yahoo<dot>com
----
"Nick" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks