Hi JMB,
I tried the Average(If)... below, but had to modify it slightly because the
data is not really in two different sheets - the data is lined up next to
each other in the same sheet. In any event, when I tried it I got a #VALUE!
error in the cell with that formula. Maybe I modified it incorrectly. I
replied to the other person who put me on to the sumproduct formula (which
worked, by the way - though I haven't tested it with blank cells or E in the
cell) and laid my data out differently side by side showing the letters and
numbers of the cells. It might be easier to advise me if you see the setup I
sent in my reply to Ragdyer (RD). I'd be interested in your solution as
well. Actually, I want to include two blank cells on the outside of my
labels which will be colored in as markers so that if I insert more columns,
it would be done inbetween the data. I don't know if that makes sense to
you. In any event, I tried the =Average(If ... statement with and without
the blank cells on either end and it still got the #VALUE! error. Any advice?

Thanks - Steve

"JMB" wrote:

Assuming your labels (HW, Q, T) are in B1:H1, J1 contains the label for what
you want to average (eg HW) and your first row of data is in B2:H2, you could
try:

=AVERAGE(IF($B$1:$H$1=J$1,$B2:$H2))

array entered w/Cntrl+Shift+Enter. Then copy it down the length of your
table. But, it would treat blank cells as 0 and include them in the average.
You could correct this by putting "E" (for excused) in the cells instead of
leaving them blank.

or you, really want to use blank cells and ignore them, you could try:
=SUMIF($B$1:$H$1,J$1,$B2:$H2)/SUMPRODUCT(--($B$1:$H$1=J$1),--($B2:$H2<""))

entered normally and copied down.




"Steve in Columbia" wrote:

Background:
I want to do this in Excel!
I have a table of weighted percentages for a grade book.
Currently they are weighted averages that look like this. They are the sum
total
of all the students' quizzes, tests, and Homework assignments based on their
weighted percentages. I'm using the sumproduct funtion in the AVG column to
calculate the average based on the percentage weights. It works very well
and is and this allows me to change the weights if I decide to do so.

Students Quizzes Tests HW AVG
25% 65% 10%

Bob 100 90 100 94
Jim 50 100 100 88
Fred 50 50 100 55

Question:
I want a way to gather all and average all the data into its respective
column from another array. For example, the following would be their scores:

Type HW Q T HW T
Bob 100% 100% 100% 100% 80%
Jim 100% 50% 100% 100% 100%
Fred 100% 50% 50% 100% 50%

All the homework (HW types) would roll up into an average in the HW column
in the first array, all the Quizes (Q types) would roll up into an average in
the Quizzes column in the first array, and all the Tests (T types) would do
the same in the tests column. Also - as you can see, the different types are
not necessarily next to each other. Furthermore, If, for example, Fred only
has one HW in, I want to be able to leave the other HWs blank if I choose to
excuse him from that assignment. The AVERAGE function will do that just
fine, but I put this down in case the solution doesn't include that function.

Any ideas anyone? This would be a life saver for me and I'd be ever so
grateful. I looked for a function in excel but couldn't find one.

Thanks - Steve