Include calculations in the lookup function

  • Thread starter Thread starter JP Ronse
  • Start date Start date
Status
Not open for further replies.
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
 
Hi All,

Please ignore, it is posted to the wrong group. My appologies.

Wkr,

JP

"JP Ronse" <fb893760@skynet.be> wrote in message

news:%23oQWqsXVKHA.5368@TK2MSFTNGP02.phx.gbl...
> 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
>
 
Status
Not open for further replies.

Similar threads

Back
Top