Average Function with Day of Week Criteria

This is for Excel 2000.

I want to compute averages based on the day of the week listed in another
column but within the same row. I already have the day of the week figured
out, column A has numbers 1-7, 1=Sunday, 2=Monday, etc.

What I want is to take the AVERAGE of B1:B200, *if* the value in column A=1,
or whatever number or numbers I specify.

One way ..

Put in C1: =ROW()

Put in D1, and array-enter (press CTRL+SHIFT+ENTER):
=AVERAGE(IF(($B$1:$B$200<>"")*($A$1:$A$200=C1),$B$1:$B$200))

Select C1:D1, copy down to D7

D1 to D7 will return the results
for the corresponding day of week listed in C1:C7
(You'd get the full list for days 1 - 7)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cheese" <[email protected]> wrote in message
news:[email protected]...
> This is for Excel 2000.
>
> I want to compute averages based on the day of the week listed in another
> column but within the same row. I already have the day of the week figured
> out, column A has numbers 1-7, 1=Sunday, 2=Monday, etc.
>
> What I want is to take the AVERAGE of B1:B200, *if* the value in column
A=1,
> or whatever number or numbers I specify.

... and a quick sample, in case needed:
http://cjoint.com/?lck01cr7y4
Cheese_newusers.xls

Note that the average formula as suggested will ignore empty cells or
formula cells within B1:B200 evaluating to null (""), re - the condition:
.... ($B$1:$B$200<>"") ..

If we need it to ignore cells containing zeros as well,
then we could add-on say, the condition: .. ($B$1:$B$200>0) ..
i.e. put instead in D1, array-enter as before, and copy down to D7:

=AVERAGE(IF(($B$1:$B$200>0)*($B$1:$B$200<>"")*($A$1:$A$200=C1),$B$1:$B$200))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Max,

For the OPs information really, but it may not be appropriate to do the
blank test. If a week number is 1 and the value is blank, it may be correct
to include that in the average

=AVERAGE(IF($A$1:$A$200=C1,$B$1:$B$200))

BTW, brought a smile to my face seeing you use cjoint :-))

Regards

Bob


"Max" <[email protected]> wrote in message
news:[email protected]...
> .. and a quick sample, in case needed:
> http://cjoint.com/?lck01cr7y4
> Cheese_newusers.xls
>
> Note that the average formula as suggested will ignore empty cells or
> formula cells within B1:B200 evaluating to null (""), re - the condition:
> ... ($B$1:$B$200<>"") ..
>
> If we need it to ignore cells containing zeros as well,
> then we could add-on say, the condition: .. ($B$1:$B$200>0) ..
> i.e. put instead in D1, array-enter as before, and copy down to D7:
>
>
=AVERAGE(IF(($B$1:$B$200>0)*($B$1:$B$200<>"")*($A$1:$A$200=C1),$B$1:$B$200))
>
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>

Thanks for the comments, Bob. Think the options given would enable the OP to
figure out the conditional checks required, and how to add/remove conditions
as may be required.

> BTW, brought a smile to my face seeing you use cjoint :-))
Ay, it was only the other day that I shared with Roger G the steps on using
cjoint <g>. But I guess savefile.com still remains my primary choice, due to
its other features (project/folder feature for instance).
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

"Max" <[email protected]> wrote in message
news:[email protected]...

> Ay, it was only the other day that I shared with Roger G the steps on
using
> cjoint <g>. But I guess savefile.com still remains my primary choice, due
to
> its other features (project/folder feature for instance).

Yes, savefile does seem to be more permanent, but for simple transient
files, I like the cjoint look and feel.