Index/reference formula problem

[Curioxity]

Hello,

I've been trying to work out how to fix this but I've about given up - another user on this forum kindly supplied me with a nice formula to work out the average score from a spreadsheet with input information based on exam/question results.

The first one works fine for all values of '1' but the other two with values of '0' and 'NA' don't seem to work, when I extend the range for looking up beyond what's currently on there (as contingency for when we add more information later) they seem to count blank spaces and the number gets smaller (this is only true for '0' and 'NA' values) For instance, the average score for someone who answered Q1 correct could be 18, avg. score incorrect 8, avg. score Not Attempted 17. When I extend my range for when we add more info later, it's fine for 1, but the diminishing number (it seems to be counting blanks!) means that avg incorrect can drop to something like 0.2 and NA becomes 0.4 etc.) - is there any way to make the formulas ignore blanks?


Formula in C2
=COUNTIF(INDEX(Sheet1!$D$2:$M$5,0,MATCH($A2,Sheet1!$D$1:$M$1,0)),1)
copy down

Formula in D2
=COUNTIF(INDEX(Sheet1!$D$2:$M$5,0,MATCH($A2,Sheet1!$D$1:$M$1,0)),0)
copy down

Formula in E2
=COUNTIF(INDEX(Sheet1!$D$2:$M$5,0,MATCH($A2,Sheet1!$D$1:$M$1,0)),"=")
copy down

Formula in F2
=IF(C2<>0,SUMPRODUCT(--(INDEX(Sheet1!$D$2:$M$5,0,MATCH($A2,Sheet1!$D$1:$M$1,0))=1),Sheet1!$B$2:$B$5)/SUMPRODUCT(--(INDEX(Sheet1!$D$2:$M$5,0,MATCH($A2,Sheet1!$D$1:$M$1,0))=1)),"N/A")
copy down

Formula in G2
=IF(D2<>0,SUMPRODUCT(--(INDEX(Sheet1!$D$2:$M$5,0,MATCH($A2,Sheet1!$D$1:$M$1,0))=0),Sheet1!$B$2:$B$5)/SUMPRODUCT(--(INDEX(Sheet1!$D$2:$M$5,0,MATCH($A2,Sheet1!$D$1:$M$1,0))=0)),"N/A")
copy down

Formula in H2
=IF(E2<>0,SUMPRODUCT(--(INDEX(Sheet1!$D$2:$M$5,0,MATCH($A2,Sheet1!$D$1:$M$1,0))=""),Sheet1!$B$2:$B$5)/SUMPRODUCT(--(INDEX(Sheet1!$D$2:$M$5,0,MATCH($A2,Sheet1!$D$1:$M$1,0))="")),"N/A")
copy down

Thanks,

Kate

 

[barry houdini]

Hello Kate,

Which ones don't work correctly? Are you referring to the G2 and H2 formulas?

Which version of Excel are you using, in Excel 2007 or later you might be able to simplify by using AVERAGEIF/AVERAGEIFS?

For example this formula can be used in G2

=IF(D2<>0,AVERAGEIF(INDEX(Sheet1!$D$2:$M$5,0,MATCH($A2,Sheet1!$D$1:$M$1,0)),0,Sheet1!$B$2:$B$5),"N/A")

It won't treat blanks as zeroes as your existing formula does

 

[AlphaFrog]

If you don't have Excel 2007 or later as barry houdini suggests, you could use a SUMIF/COUNTIF formula instead of your SUMPRODUCT/SUMPRODUCT formula.

Example formula in G2
=IF(D2<>0,SUMIF(INDEX(Sheet1!$D$2:$M$5,0,MATCH($A2,Sheet1!$D$1:$M$1,0)),0,Sheet1!$B$2:$B$5)/
COUNTIF(INDEX(Sheet1!$D$2:$M$5,0,MATCH($A2,Sheet1!$D$1:$M$1,0)),0),"N/A")

Better yet...
Example formula in G2
=IF(D2<>0,SUMIF(INDEX(Sheet1!$D$2:$M$5,0,MATCH($A2,Sheet1!$D$1:$M$1,0)),0,Sheet1!$B$2:$B$5)/D2,"N/A")

 

[Curioxity]

Hi,

I'm using Excel 2010 - both H2 and G2 are the falldown formulas.

Will give that new code a go tomorrow!

Thanks,

Kate

 

[Curioxity]

Worked perfectly thanks for your help!