# Index/reference formula problem

#### Curioxity

##### New Member
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

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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

Last edited:
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")

Last edited:
Hi,

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

Will give that new code a go tomorrow!

Thanks,

Kate

Worked perfectly thanks for your help!

Replies
9
Views
271
Replies
2
Views
151
Replies
8
Views
234
Replies
2
Views
262
Replies
6
Views
521

1,203,667
Messages
6,056,643
Members
444,879
Latest member
suzndush

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back