Question

AutoSave OFF Download HW2(2) - Saved to my Mac a Home Insert Draw Design Layout References Mailings Review View Tell me Share Comments Calibri (Bo... v 11 A A Aa A Lid Leاستار AalbCodle EEE 24 = = = AaBbccDc AaBbccdde: AaBb Malbec te Pastc B 1 A v Normal Ne Spacing Heading 1 Hascing 2 Title Bubtitle 30 X X Dictate Sensitivity Styles Pare Q3 Open worksheet Q3. It contains information of loan applications received. Loan applications are divided into 4 categories: A, B, Z, and Other. Their maximum limits are in the green table, next to the "Decision" column. Task 1: use vookup to retrieve information from the green table and fill in column "Maximum Loan Limit". Loan officer's decision rule is as follows: (1) if loan amount is greater than the corresponding maximum loan limit, the officer denies the application if the applicant's credit is rated as fair, or poor, but approves the application if the credit is excellent. For applicants whose have a "Good" credit rating, their applications are approved if and only if they have a waver letter from the credit department (i.e., the entry in column "Credit Waver" is "Yes"). (2) if the loan amount is less than or equal to the maximum loan limit, the officer approves the application if the applicant's credit is excellent or good, and denies the application if credit is poor. In this situation, for applicant whose credit is rated as "fair", the officer approves the application if and only if the applicant has a waver letter from the credit department (i.e., the entry in column "Credit Waver" is "Yes"). Page 2 of 3 430 wards English (United States) Focus + 153%

Answer 1

The VLOOKUP formula to capture Max Loan Limit to be entered in cell E4 is following:

=VLOOKUP(D4,$J$3:$K$6,2,), drag to copy this formula down the rows

As per Loan Officer's decision rules, conditions for approval of loan application are following:

Credit History	Loan Amount	Credit Waiver
Excellent	any	any
Good	<=Limit	any
Good	>Limit	Yes
Fair	<=Limit	Yes

In all other scenarios, the loan application is denied.

These rules are formulated as below:

Formula to be entered in H4 =IF(OR(C4="Excellent",AND(C4="Good",OR(F4<=E4,G4="Yes")),AND(C4="Fair",F4<=E4,G4="Yes")),"Approved","Denied")

An alternative formula for H4 is:

=IF(F4>E4,IF(C4="Excellent","Approved",IF(C4="Good",IF(G4="Yes","Approved","Denied"),"Denied")),IF(OR(C4="Excellent",C4="Good"),"Approved",IF(C4="Fair",IF(G4="Yes","Approved","Denied"),"Denied")))

Any of the above formulas can be used in H4 , it will give the same results.

Then drag to copy

---------------------

The formulated spreadsheet is as follows:

H4 fi =IF(OR(C4="Excellent",AND(C4="Good",OR(F4<=E4,64="Yes")),AND(C4="Fair",F4<=E4, G4="Yes")),"Approved", "Denied") A B D E G H J K L M 1 2 3 4. Category Limit B 3000 A 25000 Z 30000 Other 5000 5 6 7 8 9 10 11 Application Credit History Category Maximum Loan Limit Loan Amount Credit Waiver Decision #1 Excellent Other 5000 3000 No Approved #2 Good B 3000 8000 Yes Approved #3 Poor B 3000 6000 Yes Denied #4 Good B 3000 3000 No Approved #5 Fair Other 5000 5000 No Denied #6 Fair B 3000 1000 Yes Approved #7 Poor A 25000 1500 No Denied #8 Good А 25000 4000 Yes Approved #9 Excellent Z 30000 1500 No Approved #10 Fair А 25000 7000 No Denied #11 Good B 3000 20000 No Denied #12 Excellent Other 5000 9000 Yes Approved #13 Fair Z 30000 5000 Yes Approved #14 Good B 3000 4000 No Denied #15 Fair Other 5000 26000 Yes Denied #16 Excellent A 25000 1000 No Approved #17 Good Other 5000 27000 No Denied 12 13 14 15 16 17 18 19 20 21 22 23

FORMULA VIEW:

A B с D E F G H 1 2 3 4 5 6 7 8 9 10 11 Applic.Credit HistCategory Maximum Loan Limit Loan Amo Credit Decision #1 Excellent Other =VLOOKUP(D4,$J$3:$K$6,2) 3000 No =IF(OR(C4="Excellent", AND(C4="Good",OR(F4<=E4, G4="Yes")),AND(C4="Fair",F4<=E4, G4="Yes")),"Approved","Denied") #2 Good B =VLOOKUP(D5, $J$3:$K$6,2) 8000 Yes =IF(OR(CS="Excellent",AND(C5="Good", OR(F5<=E5, G5="Yes")), AND(C5="Fair",F5<=E5, G5="Yes")), "Approved", "Denied") #3 Poor B =VLOOKUP(D6,$J$3:$K$6,2) 6000 Yes =IF(OR(C6="Excellent", AND(C6="Good",OR(F6<=E6,66="Yes")),AND(C6="Fair",F6<=E6,66="Yes")),"Approved","Denied") #4 Good B =VLOOKUP(D7, $J$3:$K$6,2,) 3000 No =IF(OR(C7="Excellent",AND(C7="Good",OR(F7<=E7,67="Yes")), AND(C7="Fair",F7<=E7,67="Yes")),"Approved", "Denied") #5 Fair Other =VLOOKUP(D8,$J$3:$K$6,2,) 5000 No =IF(OR(C8="Excellent", AND(C8="Good",OR(F8<=E8,68="Yes")),AND(C="Fair",F8<=E8,68="Yes")), "Approved","Denied") #6 Fair B =VLOOKUP(D9, $J$3:$K$6,2) 1000 Yes =IF(OR(C9="Excellent",AND(C9="Good",OR(F9<=E9,69="Yes")), AND(C9="Fair",F9<=E9,69="Yes")),"Approved", "Denied") #7 Poor A =VLOOKUP(D10,$J$3:$K$6,2) 1500 No =IF(OR(C10="Excellent", AND(C10="Good",OR(F10<=E10,610="Yes")),AND(C10="Fair",F10<=E10,610="Yes")),"Approved", "Denied") #8 Good A =VLOOKUP(D11,$J$3:$K$6,2) 4000 Yes =IF(OR(C11="Excellent", AND(C11="Good",OR(F11<=E11,611="Yes")), AND(C11="Fair",F11<=E11,611="Yes")),"Approved","Denied") #9 Excellent Z =VLOOKUP(D12,$J$3:$K$6,2) 1500 No =IF(OR(C12="Excellent", AND(C12="Good",OR(F12<=E12,612="Yes")),AND(C12="Fair",F12<=E12,612="Yes")),"Approved", "Denied") #10 Fair A =VLOOKUP(D13,$J$3:$K$6,2) 7000 No =IF(OR(C13="Excellent", AND(C13="Good",OR(F13<=E13,613="Yes")), AND(C13="Fair",F13<=E13,613="Yes")),"Approved","Denied") #11 Good B =VLOOKUP(D14,$J$3:$K$6,2,) 20000 No =IF(OR(C14="Excellent", AND(C14="Good",OR(F14<=E14,614="Yes")),AND(C14="Fair",F14<=E14,614="Yes")),"Approved", "Denied") #12 Excellent Other =VLOOKUP(D15,$J$3:$K$6,2,) 9000 Yes =IF(OR(C15="Excellent", AND(C15="Good",OR(F15<=E15,615="Yes")), AND(C15="Fair",F15<=E15,615="Yes")),"Approved","Denied") #13 Fair Z =VLOOKUP(D16,$J$3:$K$6,2) 5000 Yes =IF(OR(C16="Excellent", AND(C16="Good",OR(F16<=E16,616="Yes")),AND(C16="Fair",F16<=E16,616="Yes")),"Approved","Denied") #14 Good B =VLOOKUP(D17,$J$3:$K$6,2,) 4000 No =IF(OR(C17="Excellent", AND(C17="Good",OR(F17<=E17,617="Yes")), AND(C17="Fair",F17<=E17,617="Yes")),"Approved","Denied") #15 Fair Other =VLOOKUP(D18,$J$3:$K$6,2,) 26000 Yes =IF(OR(C18="Excellent", AND(C18="Good",OR(F18<=E18,618="Yes")),AND(C18="Fair",F18<=E18,618="Yes")),"Approved", "Denied") #16 Excellent A =VLOOKUP(D19,$J$3:$K$6,2) 1000 No =IF(OR(C19="Excellent", AND(C19="Good", OR(F19<=E19,619="Yes")), AND(C19="Fair",F19<=E19,619="Yes")), "Approved","Denied") #17 Good =VLOOKUP(D20,$J$3:$K$6,2, 27000 No =IF(OR(C20="Excellent",AND(C20="Good",OR(F20<=E20,620="Yes")), AND(C20="Fair",F20<=E20,620="Yes")),"Approved","Denied") 12 13 14 15 16 17 Other 18 19 20 21 22 23