The VLOOKUP function is one of Excel's little gems. Imagine having a list of students whom you have to assign a letter grade to. One way is to use an IF function or maybe a COUNTIF. Let me introduce you to a third and easier way.
In a part of your worksheet, insert a criteria table in which you indicate the cut off marks and letter grade to be assigned at each grade interval.
In your grade column of the marks table, simply add the function as follows:
=VLOOKUP(cell_with_mark,criteria_table_location,column_no_with_grade_letter)
- Remember to exclude the heading labels when giving your criteria table location
- It is also a good idea to make the cell references for the criteria table location absolute so that they are locked and do not change when you copy the formula
Here's a demonstration of it at work.
No comments:
Post a Comment