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.

Average Function with Day of Week Criteria
 

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" wrote in message
...
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.

Average Function with Day of Week Criteria
 

... 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$2000) ..
i.e. put instead in D1, array-enter as before, and copy down to D7:

=AVERAGE(IF(($B$1:$B$2000)*($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
--

Average Function with Day of Week Criteria
 

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" wrote in message
...
.. 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$2000) ..
i.e. put instead in D1, array-enter as before, and copy down to D7:


=AVERAGE(IF(($B$1:$B$2000)*($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
--

Average Function with Day of Week Criteria
 

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

Average Function with Day of Week Criteria
 

"Max" wrote in message
...

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.

Average Function with Day of Week Criteria
 

Thanks very much for your assistance. It worked great.

Average Function with Day of Week Criteria
 

"Bob Phillips" wrote:
Yes, savefile does seem to be more permanent,
but for simple transient files, I like the cjoint look and feel.
For uploading cjoint seems much more accessible,
while savefile's quite full of late
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Average Function with Day of Week Criteria
 

Glad to hear that !
Thanks for the feedback (from us <g )
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cheese" wrote in message
...
Thanks very much for your assistance. It worked great.