Nested IF Function problem for Excel 2003

[LJNagora]

I have to calculate test scores that include rewrites, but I don't want to
average in a column that's not supposed to be part of the calculation until
there's an amount filled in. I started with the following with only two
columns (N & H) being rewrite columns:

=IF(Z6>0,AVERAGE(Z6,W6,T6,Q6,IF(N6>0,N6,K6),IF(H6>0,H6,E6)),IF(W6>0,AVERAGE(W6,T6,Q6,IF(N6>0,N6,K6),IF(H6>0,H6,E6)),IF(T6>0,AVERAGE(T6,Q6,IF(N6>0,N6,K6),IF(H6>0,H6,E6)),IF(Q6>0,AVERAGE(Q6,IF(N6>0,N6,K6),IF(H6>0,H6,E6)),IF(N6>0,AVERAGE(N6,IF(H6>0,H6,E6)),IF(K6>0,AVERAGE(K6,IF(H6>0,H6,E6)),IF(H6>0,H6,IF(E6>0,E6,0))))))))

Column E is test 1
Column H is rewrite for test 1
Column K is test 2
Column N is Test 2 rewrite
Column T, Q, W, and Z are test 3, 4, 5, and 6

I have two problems with the above:

For whatever reason, column K is not being calculated at all. I've tried
many different scenarios and it still won't calculate once I add anything
before it (N, T, etc).

Problem 2: Now I have to add rewrite columns for T and Q but I don't need
test 6 anymore.

My question is can you help me with the above formula or should I use a
different formula completely?

Thank you in advance for your expertise. I can provide a copy of the sheet
if necessary.
Lynn

 

[Gary''s Student]

You need to take advantage of the fact the =AVERAGE() ignores blanks, but
includes 0's as real values. Say we use two "helper" columns col AA and col
AB

In AA6 enter:
=IF(E6 & H6="","",IF(H6<>"",H6,E6))

In AB6 enter:
=IF(K6 & N6="","",IF(N6<>"",N6,K6))

What the AA formula is really saying is that if E & H are both blank, then
show blank.

If H is not blank that show the H value, otherwise show the E value.

Then the final average should be:

=AVERAGE(T6,Q6,W6,Z6,AA6,AB6)

Use the same approach for all additional rewrite columns

 

[LJNagora]

Thanks Gary ... that helped ... one more problem ....

I now need to find the average score in the column that contains the
averages for each row. I know you can't average an average, so how can I get
the average score?

Thanks again,
Lynn

 

[Gary''s Student]

First let me commend you on your question. Most people don't know that
averaging averages is bad.

Here is one approach. Say we have data with some blanks in A1 thru D99

In A100 thru D100, we have the average of each column, so in A100 we have
=AVERAGE(A1:A99), etc.

If we want the overall average, we sould NOT average A100 thru D100, instead:

=SUM(A199)/COUNTIF(A199,"<>" & "")

We make our own average formula!

 

[LJNagora]

Since I have some columns in my data that are not counted due to the criteria
as is in the following formula for the first row:

=AVERAGE(IF(H6="",E6,H6),IF(N6="",K6,N6),IF(T6="",Q6,T6),IF(Z6="",W6,Z6),AC6) (thanks again for your help on this one)

How can I make sure the sheet knows what cells to choose?
Is there a possible way that the average score could chosen from the average
column for 26 rows? (I hope this makes sense)

Thanks again,
Lynn

 

[Gary''s Student]

You can add criteria to the average by using SUMPRODUCT() in place of SUM().

Of course, the alternative is to fragment both the numerator and demoninator
into appropriate pieces

 

[LJNagora]

Sorry Gary, either I don't understand or SUMPRODUCT() isn't working. Each
row grabs a grade from each column situation for 4 different tests (whether
it chooses a test score or a rewrite
"=AVERAGE(IF(H6="",E6,H6),IF(N6="",K6,N6),IF(T6="",Q6,T6),IF(Z6="",W6,Z6),AC6)"),
so I really don't know how to create a formula to gather that overall average
if I don't know ahead of time which column the formula for each row will
include in it's calculation.

Your formula below using SUM() will definitely come in handy for different
sheet I have, thank you.

Is there something I could include with this post to clarify?

Lynn

 

[LJNagora]

Thanks a million Gary ... It finally clicked ... it turns out I was just
missing a step.

Lynn