HI Group,
I have been asked to work out a risk assessment based on some of the
following grid/variables (there are 3 more to build...)

Loan Rating Weight Item Rating Weight
10000 1 4 Capital 1 3
25000 2 4 Revenue 2 3
50000 3 4 Unknown 3 3
75000 4 4
100000 5 4
1000000 6 4

For example, If an applicant has the following criteria...

Loan Item
15000 Revenue

....then the risk rating is (2 x 4) + (2 x 3) = 14

I have done a bit of searching and think I need Vlookup but I am not
sure to implement the logic.

Can someone help.

Thanks
Chris

Change the table slighlty

Loan Rating Weight Item Rating Weight
0 1 4 Capital 1 3
10000 2 4 Revenue 2 3
25000 3 4 Unknown 3 3
50000 4 4
75000 5 4
100000 6 4


and use

=VLOOKUP(15000,A2:C7,2,TRUE)*VLOOKUP(15000,A2:C7,3 ,TRUE)+
VLOOKUP("Revenue",D2:F4,2,FALSE)*VLOOKUP("Revenue" ,D2:F4,3,FALSE)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
oups.com...
HI Group,
I have been asked to work out a risk assessment based on some of the
following grid/variables (there are 3 more to build...)

Loan Rating Weight Item Rating Weight
10000 1 4 Capital 1 3
25000 2 4 Revenue 2 3
50000 3 4 Unknown 3 3
75000 4 4
100000 5 4
1000000 6 4

For example, If an applicant has the following criteria...

Loan Item
15000 Revenue

...then the risk rating is (2 x 4) + (2 x 3) = 14

I have done a bit of searching and think I need Vlookup but I am not
sure to implement the logic.

Can someone help.

Thanks
Chris

On Feb 15, 9:33 am, "Bob Phillips" wrote:
Change the table slighlty

Loan Rating Weight Item Rating Weight
0 1 4 Capital 1 3
10000 2 4 Revenue 2 3
25000 3 4 Unknown 3 3
50000 4 4
75000 5 4
100000 6 4

and use

=VLOOKUP(15000,A2:C7,2,TRUE)*VLOOKUP(15000,A2:C7,3 ,TRUE)+
VLOOKUP("Revenue",D2:F4,2,FALSE)*VLOOKUP("Revenue" ,D2:F4,3,FALSE)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message

oups.com...



HI Group,
I have been asked to work out a risk assessment based on some of the
following grid/variables (there are 3 more to build...)

Loan Rating Weight Item Rating Weight
10000 1 4 Capital 1 3
25000 2 4 Revenue 2 3
50000 3 4 Unknown 3 3
75000 4 4
100000 5 4
1000000 6 4

For example, If an applicant has the following criteria...

Loan Item
15000 Revenue

...then the risk rating is (2 x 4) + (2 x 3) = 14

I have done a bit of searching and think I need Vlookup but I am not
sure to implement the logic.

Can someone help.

Thanks
Chris- Hide quoted text -

- Show quoted text -

HI Bob,
Thanks for the reply and suggestion.
I was hoping maybe for a more dynamic solution as I have to apply this
logic to 1500 applications which span 5 sets of text variables. If you
could supply the basics I was hoping to extend it to 5 sets of
variables.

Thanks
Chris

How about insert 1 column in both sheets combine these 2 together say:

Index Loan Rating Weight Item Rating Weight
10000Capital 10000 1 4 Capital 1 3
25000Revenue 25000 2 4 Revenue 2 3

and vlookup the index

" wrote:

HI Group,
I have been asked to work out a risk assessment based on some of the
following grid/variables (there are 3 more to build...)

Loan Rating Weight Item Rating Weight
10000 1 4 Capital 1 3
25000 2 4 Revenue 2 3
50000 3 4 Unknown 3 3
75000 4 4
100000 5 4
1000000 6 4

For example, If an applicant has the following criteria...

Loan Item
15000 Revenue

....then the risk rating is (2 x 4) + (2 x 3) = 14

I have done a bit of searching and think I need Vlookup but I am not
sure to implement the logic.

Can someone help.

Thanks
Chris

Could you expand, that looks pretty dynamic to me. You don't have to
hard-code the test values, you could put those in cells and reference the
cells.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
ups.com...
On Feb 15, 9:33 am, "Bob Phillips" wrote:
Change the table slighlty

Loan Rating Weight Item Rating Weight
0 1 4 Capital 1 3
10000 2 4 Revenue 2 3
25000 3 4 Unknown 3 3
50000 4 4
75000 5 4
100000 6 4

and use

=VLOOKUP(15000,A2:C7,2,TRUE)*VLOOKUP(15000,A2:C7,3 ,TRUE)+
VLOOKUP("Revenue",D2:F4,2,FALSE)*VLOOKUP("Revenue" ,D2:F4,3,FALSE)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

oups.com...



HI Group,
I have been asked to work out a risk assessment based on some of the
following grid/variables (there are 3 more to build...)

Loan Rating Weight Item Rating Weight
10000 1 4 Capital 1 3
25000 2 4 Revenue 2 3
50000 3 4 Unknown 3 3
75000 4 4
100000 5 4
1000000 6 4

For example, If an applicant has the following criteria...

Loan Item
15000 Revenue

...then the risk rating is (2 x 4) + (2 x 3) = 14

I have done a bit of searching and think I need Vlookup but I am not
sure to implement the logic.

Can someone help.

Thanks
Chris- Hide quoted text -

- Show quoted text -

HI Bob,
Thanks for the reply and suggestion.
I was hoping maybe for a more dynamic solution as I have to apply this
logic to 1500 applications which span 5 sets of text variables. If you
could supply the basics I was hoping to extend it to 5 sets of
variables.

Thanks
Chris

On Feb 15, 11:12 am, "Bob Phillips" wrote:
Could you expand, that looks pretty dynamic to me. You don't have to
hard-code the test values, you could put those in cells and reference the
cells.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message

ups.com...



On Feb 15, 9:33 am, "Bob Phillips" wrote:
Change the table slighlty

Loan Rating Weight Item Rating Weight
0 1 4 Capital 1 3
10000 2 4 Revenue 2 3
25000 3 4 Unknown 3 3
50000 4 4
75000 5 4
100000 6 4

and use

=VLOOKUP(15000,A2:C7,2,TRUE)*VLOOKUP(15000,A2:C7,3 ,TRUE)+
VLOOKUP("Revenue",D2:F4,2,FALSE)*VLOOKUP("Revenue" ,D2:F4,3,FALSE)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

groups.com...

HI Group,
I have been asked to work out a risk assessment based on some of the
following grid/variables (there are 3 more to build...)

Loan Rating Weight Item Rating Weight
10000 1 4 Capital 1 3
25000 2 4 Revenue 2 3
50000 3 4 Unknown 3 3
75000 4 4
100000 5 4
1000000 6 4

For example, If an applicant has the following criteria...

Loan Item
15000 Revenue

...then the risk rating is (2 x 4) + (2 x 3) = 14

I have done a bit of searching and think I need Vlookup but I am not
sure to implement the logic.

Can someone help.

Thanks
Chris- Hide quoted text -

- Show quoted text -

HI Bob,
Thanks for the reply and suggestion.
I was hoping maybe for a more dynamic solution as I have to apply this
logic to 1500 applications which span 5 sets of text variables. If you
could supply the basics I was hoping to extend it to 5 sets of
variables.

Thanks
Chris- Hide quoted text -

- Show quoted text -

Thank you Bob, never thought I could change your coded values...doh!!!

One more thing, some cells have blanks so my formulas return #N/A.,
how can I avoid this scenario now?
Here is how my code looks so far (with range names)

=VLOOKUP(C7,Expenditure,2,TRUE)*VLOOKUP(C7,Expendi ture,3,TRUE)+
VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,Project Type,3,FALSE)
+VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,Measure Body,3,TRUE)+
VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,Fina lRecipient,3,FALSE)

The normal way to handle #N/A is as follows

=IF(ISNA(vlookup_formula),"",vlookup_formula)

You can do all 8 at once

=IF(ISNA(VLOOKUP(C7,Expenditure,2,TRUE)*VLOOKUP(C7 ,Expenditure,3,TRUE)+
VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,Project Type,3,FALSE)+
VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,MeasureB ody,3,TRUE)+
VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,Fina lRecipient,3,FALSE)),"",
VLOOKUP(C7,Expenditure,2,TRUE)*VLOOKUP(C7,Expendit ure,3,TRUE)+
VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,Project Type,3,FALSE)+
VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,MeasureB ody,3,TRUE)+
VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,Fina lRecipient,3,FALSE))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
oups.com...
On Feb 15, 11:12 am, "Bob Phillips" wrote:
Could you expand, that looks pretty dynamic to me. You don't have to
hard-code the test values, you could put those in cells and reference the
cells.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

ups.com...



On Feb 15, 9:33 am, "Bob Phillips" wrote:
Change the table slighlty

Loan Rating Weight Item Rating Weight
0 1 4 Capital 1 3
10000 2 4 Revenue 2 3
25000 3 4 Unknown 3 3
50000 4 4
75000 5 4
100000 6 4

and use

=VLOOKUP(15000,A2:C7,2,TRUE)*VLOOKUP(15000,A2:C7,3 ,TRUE)+
VLOOKUP("Revenue",D2:F4,2,FALSE)*VLOOKUP("Revenue" ,D2:F4,3,FALSE)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

groups.com...

HI Group,
I have been asked to work out a risk assessment based on some of the
following grid/variables (there are 3 more to build...)

Loan Rating Weight Item Rating Weight
10000 1 4 Capital 1 3
25000 2 4 Revenue 2 3
50000 3 4 Unknown 3 3
75000 4 4
100000 5 4
1000000 6 4

For example, If an applicant has the following criteria...

Loan Item
15000 Revenue

...then the risk rating is (2 x 4) + (2 x 3) = 14

I have done a bit of searching and think I need Vlookup but I am not
sure to implement the logic.

Can someone help.

Thanks
Chris- Hide quoted text -

- Show quoted text -

HI Bob,
Thanks for the reply and suggestion.
I was hoping maybe for a more dynamic solution as I have to apply this
logic to 1500 applications which span 5 sets of text variables. If you
could supply the basics I was hoping to extend it to 5 sets of
variables.

Thanks
Chris- Hide quoted text -

- Show quoted text -

Thank you Bob, never thought I could change your coded values...doh!!!

One more thing, some cells have blanks so my formulas return #N/A.,
how can I avoid this scenario now?
Here is how my code looks so far (with range names)

=VLOOKUP(C7,Expenditure,2,TRUE)*VLOOKUP(C7,Expendi ture,3,TRUE)+
VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,Project Type,3,FALSE)
+VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,Measure Body,3,TRUE)+
VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,Fina lRecipient,3,FALSE)

On Feb 15, 4:34 pm, "Bob Phillips" wrote:
The normal way to handle #N/A is as follows

=IF(ISNA(vlookup_formula),"",vlookup_formula)

You can do all 8 at once

=IF(ISNA(VLOOKUP(C7,Expenditure,2,TRUE)*VLOOKUP(C7 ,Expenditure,3,TRUE)+
VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,Project Type,3,FALSE)+
VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,MeasureB ody,3,TRUE)+
VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,Fina lRecipient,3,FALSE)),"",
VLOOKUP(C7,Expenditure,2,TRUE)*VLOOKUP(C7,Expendit ure,3,TRUE)+
VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,Project Type,3,FALSE)+
VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,MeasureB ody,3,TRUE)+
VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,Fina lRecipient,3,FALSE))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message

oups.com...



On Feb 15, 11:12 am, "Bob Phillips" wrote:
Could you expand, that looks pretty dynamic to me. You don't have to
hard-code the test values, you could put those in cells and reference the
cells.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

roups.com...

On Feb 15, 9:33 am, "Bob Phillips" wrote:
Change the table slighlty

Loan Rating Weight Item Rating Weight
0 1 4 Capital 1 3
10000 2 4 Revenue 2 3
25000 3 4 Unknown 3 3
50000 4 4
75000 5 4
100000 6 4

and use

=VLOOKUP(15000,A2:C7,2,TRUE)*VLOOKUP(15000,A2:C7,3 ,TRUE)+
VLOOKUP("Revenue",D2:F4,2,FALSE)*VLOOKUP("Revenue" ,D2:F4,3,FALSE)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

groups.com...

HI Group,
I have been asked to work out a risk assessment based on some of the
following grid/variables (there are 3 more to build...)

Loan Rating Weight Item Rating Weight
10000 1 4 Capital 1 3
25000 2 4 Revenue 2 3
50000 3 4 Unknown 3 3
75000 4 4
100000 5 4
1000000 6 4

For example, If an applicant has the following criteria...

Loan Item
15000 Revenue

...then the risk rating is (2 x 4) + (2 x 3) = 14

I have done a bit of searching and think I need Vlookup but I am not
sure to implement the logic.

Can someone help.

Thanks
Chris- Hide quoted text -

- Show quoted text -

HI Bob,
Thanks for the reply and suggestion.
I was hoping maybe for a more dynamic solution as I have to apply this
logic to 1500 applications which span 5 sets of text variables. If you
could supply the basics I was hoping to extend it to 5 sets of
variables.

Thanks
Chris- Hide quoted text -

- Show quoted text -

Thank you Bob, never thought I could change your coded values...doh!!!

One more thing, some cells have blanks so my formulas return #N/A.,
how can I avoid this scenario now?
Here is how my code looks so far (with range names)

=VLOOKUP(C7,Expenditure,2,TRUE)*VLOOKUP(C7,Expendi ture,3,TRUE)+
VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,Project Type,3,FALSE)
+VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,Measure Body,3,TRUE)+
VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,Fina lRecipient,3,FALSE)- Hide quoted text -

- Show quoted text -

Bob,
Just back to work...works perfect...thanks very much
Chris.