Returning the column of search text

[Joel Green]

The basic problem is that I need to search for all the instances of a
specified text string within a range and return the column so I can pull the
value of another cell in the same column.
The worksheet is used for student test marks.

Here's the setup:
A B C D
1 10 20
2 student x 7 15
3 student y 5 o
4 student z 8 17

D2 = IF(SUM(B2:C2)=0,0,AVERAGE((SUM(B2:C2)/SUM(B$1:C$1))*100))

Row 1 represents what the test was out of
An o represents that the student is omitted from the test.

If want to add something to this formula to remove 20 from the total
possible test score for student y.

I know it's really easy to do this programatically, but I'd rather keep it
in the cells if possible.

Any suggestions appreciated.

Thanks.

 

[Frank Kabel]

Hi
try:
D2 =
IF(SUM(B2:C2)=0,0,(SUM(B2:C2)/SUMPRODUCT(--(ISNUMBER(B2:C2)),$B$1:$C$1)
)*100)

 

[Joel Green]

Thanks Frank, it worked perfectly - one question...

What is the purpose of the '--' before ISNumber. I know the formula doesn't
work without it, but I can't find a reason in help as to why or what it
does.

Thanks,
Joel

 

[Frank Kabel]

Hi
the '--' (double minus) in this case is used to convet the number
returned as string value to a real number. You could achieve the sam
with VALUE(...)

 

[Joel Green]

Thanks again.

Joel

Hi
the '--' (double minus) in this case is used to convet the number
returned as string value to a real number. You could achieve the sam
with VALUE(...)