View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gary''s Student Gary''s Student is offline
external usenet poster
 
Posts: 11,058
Default 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