Question

Please provide Excel formula and show in Excel.

In addition to the school-based financial aid, the department of finance and marketing have their own extra budget to offer financial aid to the students based on their GMAT score. Write a function in cell I2 that returns “not qualified” if the GMAT score of the applicants is below 600 or they are from Information Systems department. Otherwise, the function should assign the appropriate scholarship to the applicants of each department based on the GMAT score.

J K N GMAT score Department based Financial aid Origin Domestic 567 International 553 Domestic 645 Hlook 1 School Based Financial aid Look up International 741 International GPA Score- 680 80 85 90 607 100% of tuition International 50% of tuition 80% of tuition International 619 International 720 International Hlookup 2_Department Based Financial aid 10 666 International 11 611 GMAT Score--- 600 650 700 $1,000 $2,000 $5,000 $4,000 $10,000 $8,000 12 International 691 Department of Finance financial aid- 13 Domestic 628 Department of Marketing financial aid- 14 Domestic 700 15 Domestic 723 16 Domestic 627 Domestic 17 655 International 18 631 19 Domestic 670 20 Domestic 590 21 Domestic 589

Answer 1

Please do Upvote if you are served. Feel free to reach out in the comments

Cheers!!!

Answer:

Assuming 'C' is the column containing the subjects Finance, Marketing and Information systems,

The cell formula to be inserted in I2 is

=IF(AND(C2="Finance",AND(E2>599,E2<649)),"1000",IF(AND(C2="Finance",AND(E2>649,E2<699)),"5000",IF(AND(C2="Finance",AND(E2>699,E2<900)),"10000",IF(AND(C2="Marketing",AND(E2>599,E2<649)),"2000",IF(AND(C2="Marketing",AND(E2>649,E2<699)),"4000",IF(AND(C2="Marketing",AND(E2>699,E2<900)),"8000","Not Qualified"))))))