Problem in Excel using IF and LOOKUP Function

A list of students with their periodic exam (Prelim, Midterm, Pre – final, and Final) to calculate the average, equivalent, and remarks using excel. This problem it might be use various built-in function in excel such as AVERAGE, IF, and LOOKUP Function. Every Function has special meaning and capability to perform calculation. For instance, to identify whether the student’s Passed or Failed, we need to use IF Function. Now, find out what’s the best solution of this problem.

Given the following table. 

Grading System:

Grade                    Equivalent

100                        1.0

97-99                    1.25

94-96                    1.50

91-93                    1.75

88-90                    2.00

85-87                    2.25

82-84                    2.50

79-81                    2.75

75-78                    3.00

<75                        5.00

Requirements:

1.   Compute the Average grade of every student.

2.   Determine the student’s Equivalent grade given the Grading System above and Remarks whether Failed or Passed using IF Function.

3.   Determine the name of a student whose Average is equals to 100 using LOOKUP Function.

4.   Determine the name of a student got Failed Remarks using LOOKUP Function.

Final Output looks like the following