Include calculations in the lookup function

  • Thread starter Thread starter JP Ronse
  • Start date Start date
J

JP Ronse

Hi All,

I am working on some complex functions to evaluate results and return a
letter from A to E (Excellent to Unsatisfactory).

In my sample, I have the range D5:D20, containing 7 scores from A to E.

With Counta(D5:D20) I can count the number of scores.

With {=SUM(IF(ISERROR(CODE(D5:D20));FALSE;CODE(D5:D20)-64))} I am able to
calculate a value based on A=1, B=2 ... E=5.

I am now looking to return a letter based on the result of the above
function and was trying witk lookup

=LOOKUP(E22;{0;7;10,51;17,51;24,51;31,51};{"";"A";"B";"C";"D";"E"})

Herein is 0 and "" a workaround to suppress error indications.
7 to 10,5 is the A range
10,51 to 17,5 is in the B range


With E22 = sum function

As long as I write hte lookup function this way, it is working but I can not
include calculations in it, e.g.

=lookup(E22;{0;Counta(D5:D20);Counta(D5:D20)+Counta(D5:D20/2+0,01; ...};{})

I need this approach because the number of scores is not constant. Any
suggestion? Thnaks in advance.

Please note that my locale is using ";" instead of ",".

JP Ronse
 
Back
Top