Nested IF Function problem for Excel 2003
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
--
Gary''s Student - gsnu200909
"LJNagora" wrote:
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(Z60,AVERAGE(Z6,W6,T6,Q6,IF(N60,N6,K6),IF(H6 0,H6,E6)),IF(W60,AVERAGE(W6,T6,Q6,IF(N60,N6,K6), IF(H60,H6,E6)),IF(T60,AVERAGE(T6,Q6,IF(N60,N6,K 6),IF(H60,H6,E6)),IF(Q60,AVERAGE(Q6,IF(N60,N6,K 6),IF(H60,H6,E6)),IF(N60,AVERAGE(N6,IF(H60,H6,E 6)),IF(K60,AVERAGE(K6,IF(H60,H6,E6)),IF(H60,H6, IF(E60,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
|