Excel Formula for Grading Marks

Objective

To create an Excel formula that evaluates a total score out of 15 (where 100% equals 45) and assigns a letter grade based on specified score ranges.

Grading Criteria

• A: Score > 42
• B: Score > 33
• C: Score > 24
• D: Score > 15
• E: Score > 9
• N: Score = 0

Excel Formula

The following formula can be used to achieve the desired grading logic:

=IF(A1=0, "N", IF(A1>42, "A", IF(A1>33, "B", IF(A1>24, "C", IF(A1>15, "D", IF(A1>9, "E", ""))))))

In this formula:

• A1 is the cell reference where the score (out of 15) is located. It should be replaced with the actual cell reference as needed.

Explanation of the Formula

1. IF(A1=0, "N", ... ): Checks if the score is 0. If true, it returns "N".
2. IF(A1>42, "A", ... ): Checks if the score is greater than 42. If true, it returns "A".
3. IF(A1>33, "B", ... ): If the previous condition is false, this checks if the score is greater than 33. If true, it returns "B".
4. IF(A1>24, "C", ... ): Continues to check for scores greater than 24, returning "C" if true.
5. IF(A1>15, "D", ... ): Checks if the score is greater than 15. If true, it returns "D".
6. IF(A1>9, "E", "" ): Finally, it checks for scores greater than 9, returning "E". If none of the conditions are met, it returns an empty string "".

Practical Example

Assuming cell A1 contains the score:

• If A1 = 44 → Formula returns "A"
• If A1 = 35 → Formula returns "B"
• If A1 = 28 → Formula returns "C"
• If A1 = 16 → Formula returns "D"
• If A1 = 10 → Formula returns "E"
• If A1 = 0 → Formula returns "N"

Conclusion

This formula effectively categorizes individual scores into a grading system based on predefined thresholds, making it suitable for academic assessments or performance evaluations. For further enhancement and learning in data analysis, consider exploring courses available on the Enterprise DNA Platform.