PLEASE, SOMEONE, HELP FINDING THE FORMULAS FOR RULES 1 TO 5 AND MAX LOAN. THAT'S ALL. THANKS IN ADVANCE.

INSTRUCTIONS:

Rule 1: the applicant can borrow at 5.5% rate if credit score is above 620, has no down payment, job category is 1 or better, there is a steady employment at least during the past year, annual income is at least $15,000, and debt to income ratio is less than 10%.

Rule 2: the applicant can borrow at 4.5% rate 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 at least for the past two years, annual income is at least $30,000, and debt to income ratio is less than 20%

Rule 3: the applicant can borrow at 4% rate if credit score is above 730, ready for a down payment of at least 15%, job category is 2 or better, there is a steady employment for at least the past 3 years, annual income is at least $60,000, and debt to income ratio is less than 30%

Rule 4: the applicant can borrow at 3.5% rate if credit score is above 780, ready for a down payment of at least 15%, job category is 3 or better, there is a steady employment at least for the past 5 years, annual income is at least $70,000, and debt to income ratio is less than 20%

Rule 5: applicant can borrow at 3% rate if has a credit score more than 800, job category is 4, and either has a steady employment history of at least 5 years or annual income of at least $80,000. There is no need to check for applicant down payment and debt ratio in this category.

• Create a reference table for rules. All numbers in your formulas should reference cells in this table. There should be no numbers in formulas.
• Use logical operators to find applicant qualifications for each rule.
• Use a function to show the best interest rate approved in column M. In case an applicant does not qualify for any of these rules the formula should return Not qualified. Apply conditional formatting to change the fill color of the entire row for customer information if not qualified.

ALL DATA IS ADDED BASED ON THE INSTRUCTIONS, I JUST NEED THE FORMULAS.

A B D E F G H J K L M Rule 1 Rule 2 620 Rule 3 680 2 8 Rule 4 730 3 Rule 5 780 5 800 1 5 15 15 2 1 2 3 4 1 2 Min Credit 3 Employed yrs 4 Down Payment 5 Job Category 6 Debt Ratio 7 Annual Income 8 Interest Rate 9 10 10 15,000 5.5 20 30,000 4.5 30 60,000 4 20 70,000 3.5 80,000 3 Rule 2 Rule 3 Rule 4 Rule 5 Max Loan 4189 11 First 12 Betty 13 Renaldo 14 Vernice 15 Elizabet 16 Shannon 17 Shasta 18 Pamelia 19 Keeley 20 Ehtel 21 Oda 22 Isadora 23 Jamar 24 Cathrine 25 Jackqueline 26 Francina 27 Cheryl 28 Kareen 29 Walter 30 Nakisha 31 Vickey 32 Miranda 33 Cristina 34 Corie 35 Tana | Credit score Employment yrs Down payment % Job category Debt 732 23 12 2 850 21 9 2 708 9 9 4 1 716 1 4 1 850 10 10 3 782 8 6 1 832 7 20 4 681 4 9 2 735 18 10 1 850 5 9 3 850 24 25 4 850 15 0 4 738 14 4 1 850 13 11 3 850 15 4 2 850 15 7 1 850 6 5 1 709 10 11 2 850 9 11 1 710 3 6 1 819 13 4 2 676 6 8 1 816 17 5 4 720 20 4 3 Annual income Rule 1 2226 98196 5009 67809 4009 29437 3110 61090 59645 955 56964 3770 108495 2914 23106 3803 81183 5908 73494 1320 84570 4910 102946 4648 66756 503 96384 5750 119198 2907 46308 5593 52360 4025 50367 806 73073 5779 42664 1322 103133 2940 64872 4791 51562 5491 75214 36 A B D E F G H J K L M Rule 1 Rule 2 620 Rule 3 680 2 8 Rule 4 730 3 Rule 5 780 5 800 1 5 15 15 2 1 2 3 4 1 2 Min Credit 3 Employed yrs 4 Down Payment 5 Job Category 6 Debt Ratio 7 Annual Income 8 Interest Rate 9 10 10 15,000 5.5 20 30,000 4.5 30 60,000 4 20 70,000 3.5 80,000 3 Rule 2 Rule 3 Rule 4 Rule 5 Max Loan 4189 11 First 12 Betty 13 Renaldo 14 Vernice 15 Elizabet 16 Shannon 17 Shasta 18 Pamelia 19 Keeley 20 Ehtel 21 Oda 22 Isadora 23 Jamar 24 Cathrine 25 Jackqueline 26 Francina 27 Cheryl 28 Kareen 29 Walter 30 Nakisha 31 Vickey 32 Miranda 33 Cristina 34 Corie 35 Tana | Credit score Employment yrs Down payment % Job category Debt 732 23 12 2 850 21 9 2 708 9 9 4 1 716 1 4 1 850 10 10 3 782 8 6 1 832 7 20 4 681 4 9 2 735 18 10 1 850 5 9 3 850 24 25 4 850 15 0 4 738 14 4 1 850 13 11 3 850 15 4 2 850 15 7 1 850 6 5 1 709 10 11 2 850 9 11 1 710 3 6 1 819 13 4 2 676 6 8 1 816 17 5 4 720 20 4 3 Annual income Rule 1 2226 98196 5009 67809 4009 29437 3110 61090 59645 955 56964 3770 108495 2914 23106 3803 81183 5908 73494 1320 84570 4910 102946 4648 66756 503 96384 5750 119198 2907 46308 5593 52360 4025 50367 806 73073 5779 42664 1322 103133 2940 64872 4791 51562 5491 75214 36