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)+Count a(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

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

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" 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)+Count a(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 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


"Lars-Åke Aspelin" wrote in message
...
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)+Cou nta(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

Hi Rick,

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

Wkr,

JP


"BSc Chem Eng Rick" wrote in
message ...
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" 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)+Count a(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


.

It looks like what you want is the average letter grade from the range
D5:D20?

With E22 = sum function

What is E22 summing? This:

{=SUM(IF(ISERROR(CODE(D5:D20));FALSE;CODE(D5:D20)-64))}

--
Biff
Microsoft Excel MVP


"JP Ronse" wrote in message
...
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)+Count a(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 Biff,

You are correct in understanding my question.

E22 is summing the range D5:D20 with the given sum function. The range
D5:D20 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, D5:D20 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" wrote in message
...
It looks like what you want is the average letter grade from the range
D5:D20?

With E22 = sum function

What is E22 summing? This:

{=SUM(IF(ISERROR(CODE(D5:D20));FALSE;CODE(D5:D20)-64))}

--
Biff
Microsoft Excel MVP


"JP Ronse" wrote in message
...
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)+Count a(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

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(D5:D20)<7,"",CHAR(ROUND(AVERAGE(IF(D5:D 20<"",CODE(D5:D20))),0)-(ROUND(AVERAGE(IF(D5:D20<"",CODE(D5:D20))),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.

--
Biff
Microsoft Excel MVP


"JP Ronse" wrote in message
...
Hi Biff,

You are correct in understanding my question.

E22 is summing the range D5:D20 with the given sum function. The range
D5:D20 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, D5:D20 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" wrote in message
...
It looks like what you want is the average letter grade from the range
D5:D20?

With E22 = sum function

What is E22 summing? This:

{=SUM(IF(ISERROR(CODE(D5:D20));FALSE;CODE(D5:D20)-64))}

--
Biff
Microsoft Excel MVP


"JP Ronse" wrote in message
...
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)+Count a(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

Try this formula to get the rounded average of the letter scores in
D5:D20

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

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

Hope this helps / Lars-Åke


On Sun, 25 Oct 2009 16:19:29 +0100, "JP Ronse"
wrote:

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


"Lars-Åke Aspelin" wrote in message
.. .
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)+Co unta(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