ignoring 0 in formulas

[Mark Horsfall]

Hello

I am teacher the children in my class do 3 tests in term. Test A, B and C

Column B shows the Percentage from the Autumn Tests and Column C shows the percentage from the spring tests.

Column B and C are an average of the three tests (A, B and C).

However I like to see the average throughout the term as they do not take the tests at the same time. So I

So I have used the following formula

{=IF(ISERROR(AVERAGE(IF(L4:N4<>0,L4:N4,""))),"",AVERAGE(IF(L4:N4<>0,L4:N4,"")))}

this enables the averages to ignore the incomplete tests such as Test C.

The problem is when a child scores a 0 in Test A it does not include this when averaging with test B

So on the spreadsheet attached Adam scored a percentage of 60% but his average should have been 30% (60 / 2 tests)

Can you change my formula or suggest another that might work.

Mark

 

[Chihiro]

Since you are pulling Test C% from Column J...

Leave Range J4:J9 as blank unless student took the test.

Then put this in N4 and copy down.
=IF(J4="","",J4)

Change your formula to check for <>"" instead of <>0