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