On Sun, 25 Oct 2009 15:00:48 +0100, "JP Ronse"
wrote:

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)+Coun ta(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


You may try the following workaround.

In cell E31 you put 0
In cell E32 you put =COUNTA(D5:D20)
In cell E33 you put =E32+E$32/2+0.01
Copy cell E33 down to E36

Then use the range E31:E36 as the lookup vector in your formula, like
=LOOKUP(E22;E31:E36;{"";"A";"B";"C";"D";"E"})

Hope this helps / Lars-Åke