Include calculations in the lookup function

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.
< E-MAIL ADDRESS | active inspector in memory >
Similar threads
Thread starter Title Forum Replies Date
T How can you include Junk Email in Search Results like you can include Deleted Items? Using Outlook 3
W Save and rename outlook email attachments to include domain name & date received Outlook VBA and Custom Forms 4
J Auto Forward - Include Attachment and change Subject depending on original sender Outlook VBA and Custom Forms 3
Q Undisclosed recipients does not include sender Using Outlook 1
J Forward Message after editing attachments and include edited attachments Using Outlook 5
B Auto reply using macro include original email Using Outlook 4
smokiibear How to modify Today Page to Include tasks from other task folders Using Outlook 1
K Want to include Sent emails in the Inbox Using Outlook 1
P Amend external calendar entries to include internal resources Using Outlook 1
C Include additional contact folders in user's Outlook Address Book centrally by Exchange Server Administration 4
S macro to include email signature Outlook VBA and Custom Forms 4
BretAB Is it possible to add a lookup field to a Message form? Outlook VBA and Custom Forms 4
N Outlook 2016 Address Book lookup Using Outlook 9
N Lookup Value From Excel and Reply With Matching Value Using Outlook 0
J Lookup from an Access table Outlook VBA and Custom Forms 1

Similar threads

Back
Top