LOOKUP won't count blank spaces

**aphid** · 03-29-2007, 03:08 PM

The development of my department tracking system (attached) has hit a snag and I would appreciate any ideas you may have, please, to resolve the problem.

Assessment data is added as shown - 4A, 5C, etc, into columns N, O , P, Q, R and S. I am using a lookup table to convert that into a numerical value, with the intention of adding up the total average value, shown in column T. The problem is that, if there are blank cells within the range, it seems unable to add them up. For the first student, row 2, that is fine. For the other students, because their assessments are ongoing, hence there are blank cells, I am unable to get a running score.

Hope all this makes sense.

**NBVC** · 03-29-2007, 03:22 PM

Try this formula in T2, copied down:

Please Login or Register  to view this content.

**aphid** · 03-29-2007, 04:14 PM

Many thanks for this. Worked treat. I have never used 'Match' before.

It has, however, thrown up another issue....

I added /6 at the end of the formula to take account of the number of assessments (6) that will eventually be added to the sheet for each student by the end of the year. However, the way it is at the moment, all assessment results, whether for one, three or all six, are divided by 6. What I really want is for an average of the actual assessments carried out and added to the sheet, which isn't always 6. Clearly, three assessments divided by 6 is not the accurate score. For that student it should be divided by 3. Is there a way of achieving this...so the formula divides the overall score by the number of assessments....which can range from none through to all six?

Is there a solution to this, also...?

**VBA Noob** · 03-29-2007, 04:23 PM

Try

=COUNTA(N2:S2)

VBA Noob

**aphid** · 03-29-2007, 04:32 PM

Where would I put 'COUNTA' ...?

=(IF(ISNUMBER(MATCH(N3,'LookUp Tables'!$A$2:$A$29)),LOOKUP(N3,'LookUp Tables'!$A$2:$B$29),0)+(IF(ISNUMBER(MATCH(O3,'LookUp Tables'!$A$2:$A$29)),LOOKUP(O3,'LookUp Tables'!$A$2:$B$29),0)+(IF(ISNUMBER(MATCH(P3,'LookUp Tables'!$A$2:$A$29)),LOOKUP(P3,'LookUp Tables'!$A$2:$B$29),0)+(IF(ISNUMBER(MATCH(Q3,'LookUp Tables'!$A$2:$A$29)),LOOKUP(Q3,'LookUp Tables'!$A$2:$B$29),0)+(IF(ISNUMBER(MATCH(R3,'LookUp Tables'!$A$2:$A$29)),LOOKUP(R3,'LookUp Tables'!$A$2:$B$29),0)+(IF(ISNUMBER(MATCH(S3,'LookUp Tables'!$A$2:$A$29)),LOOKUP(S3,'LookUp Tables'!$A$2:$B$29),0)))))))/6

**VBA Noob** · 03-29-2007, 04:34 PM

Replace /6 at the end with /COUNTA(N2:S2)

VBA Noob

**daddylonglegs** · 03-29-2007, 05:20 PM

My general rule of thumb is that if your formula obscures most of the worksheet.....it's too large....formula surgery required.....

As you're trying to average the grades AVERAGE function is a candidate, e.g. in T2 copied down

=AVERAGE(IF(ISNUMBER(MATCH(N2:S2,'LookUp Tables'!A$2:A$29)),LOOKUP(N2:S2,'LookUp Tables'!A$2:B$29),""))

confirmed with CTRL+SHIFT+ENTER

but probably a better option because

1) it doesn't require your lookup table to be sorted
2) it doesn't require CTRL+SHIFT+ENTER
3) it won't return #DIV/0! if N2:S2 is empty
4) it's shorter again

=SUMPRODUCT(SUMIF('LookUp Tables'!A$2:A$29,N2:S2,'LookUp Tables'!B$2:B$29))/MAX(1,COUNTA(N2:S2))

**aphid** · 03-29-2007, 05:46 PM

Thanks, guys. You have both provided me with two really great options. Many thanks.

Both options seem to work (I just need to test with a few examples) , i.e.

Replacing /6 with /COUNTA(N2:S2)

and....

=SUMPRODUCT(SUMIF('LookUp Tables'!A$2:A$29,N2:S2,'LookUp Tables'!B$2:B$29))/MAX(1,COUNTA(N2:S2))

The latter is clearly a much more precise solution. I just need to get my head around how it works....but it does.