Include calculations in the lookup function

[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 D520, containing 7 scores from A to E.

With Counta(D520) I can count the number of scores.

With {=SUM(IF(ISERROR(CODE(D520));FALSE;CODE(D520)-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(D520);Counta(D520)+Counta(D520/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

 

[Lars-Åke Aspelin]

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 D520, containing 7 scores from A to E.

With Counta(D520) I can count the number of scores.

With {=SUM(IF(ISERROR(CODE(D520));FALSE;CODE(D520)-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(D520);Counta(D520)+Counta(D520/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(D520)
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

 

[BSc Chem Eng Rick]

When you enter your LOOKUP function, effect it by pressing Ctrl+Shift+Enter
instead of pressing just enter.

If this helps, please click "Yes"
<><><><><><><><><><><><>

 

[JP Ronse]

Hi Lars-Åke

Thanks for your feedback.

In fact, I was avoiding subdata like you propose, because my colleagues do
not have always enough experience with Excel functions. Therefore I was
looking to 1 big function to calculate it all at once. Something like "the
master has said and so you have to believe..."

But your idea is very good to include this into the worksheet. It will show
the criterions. Thanks for the idea.

Have a nice Sunday.

Wkr,

JP

 

[JP Ronse]

Hi Rick,

I tried and no result, but I think Lookup can't calculate within {}.

Wkr,

JP

 

[T. Valko]

It looks like what you want is the average letter grade from the range
D520?

With E22 = sum function

What is E22 summing? This:

{=SUM(IF(ISERROR(CODE(D520));FALSE;CODE(D520)-64))}

 

[JP Ronse]

Hi Biff,

You are correct in understanding my question.

E22 is summing the range D520 with the given sum function. The range
D520 has some cells, not all, containing a letter from A to E. So
translating the letter to a figure from 1 to 5, will return me a value.

This value is within the range COUNTA(D5/D20)*n+COUNTA(D5/D20)/2+0,01, e.g.

7 to 10,5 is the A range
10,51 to 17,5 is in the B range

I have forgotten to say that in my example, D520 contained 7 scores!!!
Sorry for this.

So my sum function will return f.i. 13,5 with is in the B-range and I'm
looking to return B, but obviously lookup cannot return this when the first
array is based on functions?

Thanks for you help on a late Sunday afternoon.

Wkr,

JP

 

[T. Valko]

OK...

Based on your formulas if there are less than 7 grades in the range the
result is blank "".

Also, assuming the letter grades are entered in UPPERCASE. A = yes, a = no.

Try this array formula** :

=IF(COUNTA(D520)<7,"",CHAR(ROUND(AVERAGE(IF(D520<>"",CODE(D520))),0)-(ROUND(AVERAGE(IF(D520<>"",CODE(D520))),0)=68)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Lars-Åke Aspelin]

Try this formula to get the rounded average of the letter scores in
D520

=CHAR(64+ROUND(SUM(CODE(D520&"@")-64)/COUNTA(D520),0))

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Hope this helps / Lars-Åke