Returning the column of search text

  • Thread starter Thread starter Joel Green
  • Start date Start date
J

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.
 
Hi
try:
D2 =
IF(SUM(B2:C2)=0,0,(SUM(B2:C2)/SUMPRODUCT(--(ISNUMBER(B2:C2)),$B$1:$C$1)
)*100)
 
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
 
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(...)
 
Thanks again.

Joel

Frank Kabel said:
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(...)
Click to expand...
 
Back
Top