Please give the formula for Rule 1, 2, 3, 4, and Max. Loan. Thanks.

Mortgage loan officers consider multiple criteria for qualifying the applicants and decide about the maximum amount of loan they can have in their mortgage account. The following are major criteria: Down payment: the amount of money buyer pays at the closing day. Federal Housing Administration (FHA) requires %3.5 but a higher percentage is desirable and indicates the financial stability of the burrower Credit score: overall evaluation of financial status. Minimum acceptable score is 620 Employment history Income Job category: Determining how stable the employment is. There are four categories (1, 2, 3, 4), 1 being the lowest risk and 4, highest risk of unemployment Debt-to-income ratio: the lower the better You have a list of loan applicants and should decide about the mortgage amount they can receive. There are four rules to apply. Rule 1: the applicant can receive up to $100,000 loan If credit score is above 620, ready for a down payment of at least 5%, job category is 1, there is a steady employment during the past three years, annual income is at least $40,000, and debt to income ratio is less than 50%. If the applicant with the same credit score range and minimum 3 employment years has a job range in category 3 but the down payment is at least 8%, debt ratio is less than 0.3 and income is at least $45,000, is qualified for $100,000 loan. Rule 2: applicant can receive up to $200,000 loan If credit score is above 680, ready for a down payment of at least 8%, job category is 2 or better, there is a steady employment during the past two years, annual income is at least $55,000, and debt to income ratio is less than 50% Rule 3: applicant can receive up to $300,000 loan If credit score is above 730, ready for a down payment of at least 3.5%, job category is 2 or better, there is a steady employment during the past five years, annual income is at least $65,000, and debt to income ratio is less than 40% Rule 4: applicant can receive up to $400,000 loan If credit score is above 780, ready for a down payment of at least 3.5%, job category is 1, there is a steady employment during the past eight years, annual income is at least $100,000, and debt to income ratio is less than 20% Use logical operators to find applicant qualifications based on the above rules. Use a function to show the maximum amount of loan in column L. A B E F G H L M N 620 620 680 780 D Rule 1-2 Min. Credit Employed yrs Down payment Job category debt ratio Annual income 5 8 1 Rule 1-1 2 Min. Credit 3 Employed yrs 4 Down payment S Job category 6 debt ratio 7 Annual income 8 Loan 8 Rule 2 Min. Credit Employed yrs Down payment Job category debt ratio Annual income Loan Rule 3 Min. Credit Employed yrs Down payment Job category debt ratio Annual income Loan Rule 4 Min. Credit Employed yrs Down payment Job category debt ratio Annual income Loan 730 5 3.5 2 0.4 65000 300000 3 0.3 0.5 40000 100000 3.5 1 0.2 100000 400000 0.5 55000 200000 45000 10 Credit score Rule 2 Rule 3 Rule 4 Max. Loan 11 First 12 Betty 13 Vickey 14 Nakisha 15 Keeley 16 Elizabet 17 Walter 18 Miranda 19 Oda 20 Pamelia 21 Shasta 22 Jackqueline 23 Vernice 24 Jamar 25 Isadora 26 Corie 27 Tana 28 Cheryl 29 Renaldo 30 Kareen 31 Cathrine 32 Shannon 33 Cristina 34 Ehtel 35 Francina 732 850 709 850 716 850 816 710 850 708 850 782 748 850 732 738 850 720 850 Job Employment yrs Down payment % category Debt Annual income Rule 1 23 12 1 5647 130182 21 9 1 800 109735 10 11 2 3756 77242 15 2 1 1 6288 97509 1 4 1 2841 207749 15 4 1 7212 244172 17 5 4 5675 55135 3 1 1423 211491 13 11 1 3268 205927 9 1 6763 153394 5 3 7152 79084 8 1 6043 223836 4 9 2 6193 144917 24 6 1 6404 160724 7 6 2 3926 242447 14 1 404 220653 6 5 1 5285 108291 20 4 3 6047 177198 10 10 1 4053 135194 6 8 1 3459 122911 15 4. 1857 243816 13 4 2 6909 216486 18 10 1 2059 153941 9 11 1 5824 231845 676 850 819 735 850