Excel Everywhere: Performance Grading by more than VLOOKUP

functions IF, VLOOKUP, COUNTIF, SWITCH, PRODUCT, Conditional formatting, Absolute referencing, Define name, Formula builder, Wildcard, Operator

Published in

Curtis’s Digital Life

10 min readApr 29, 2019

Last article I discussed my Excel experience when doing a final year project. This time I will review some handy class of lookup function and comment on some good habits to design data protocol with Excel. Here quotes a student learning outcome assessment for teachers in the school setting.

Imagine the scenario that a primary school teacher finished marking all the exam papers of the class. That passionate educator wants to promote the pupils performance.

You may also find this article on my LinkedIn.

識睇廣東話嘅讀者亦可參考以下呢嗰版本。Cantonese readers may check out the alternative version below.

邊到都 Excel ：除咗函數 VLOOKUP 來評學生表現，仲有咩其他板斧？

上一篇文，我講咗我點樣用 Excel 來幫我搞掂嗰畢業論文一啲部分。呢次我來我就來重温一啲有用嘅 LOOKUP 函數，以及 highlight 一啲用 Excel 嘅好習慣。呢度用嗰例字講老師想檢查一下學生嘅學習成果。

medium.com

A1 Finding who had Pass/Fail by IF

To simplify, I only cover the test for the course Society here. For this part, we will first explore the initial 5 students of the name list. It is easy to apply to other columns once you know the logic behind all the functions I am going to employ.

E2 | =IF(D2>=50,"pass","fail")

There’re three major parts in the IF syntax. They are,

Cells to look at, where I set D2>=50 as the criterion
The value to be returned if the criterion is matched. The word “pass” would be shown if D2 does greater than or equal to 50.
Another value to be returned if the criterion is not matched. The word “fail” would be shown if D2 does NOT greater than or equal to 50.

A note here using a fixed constant, 50, in the syntax is a bad habit. 50 is practiced merely for smoother demonstration and I will explain later why shouldn’t do so.

A2 Make prominent who needs support by conditional formatting

After auto-filling, we now know who has managed the test and not. Then, there’s additional visualize feature to make the difference more apparent to notice by conditional formatting. The setting is quite self-explanatory. Selected the whole passSociety column, we can order Excel to highlight all the specified cells that contain “pass” with a green background. Following the same logic, we may also set additional emphasis on the “fail” grades. Below column appears after the calculation.

With highlights, the teacher now can plan on facilitating the most needed learners, like enhancing their motivation, or alternative acknowledgment. In the same time, the teacher can keep encouraging those who already beat the test to even better.

B Assigning Grades by IF

Now a new letter grading system is introduced. Marked with less than or equal to 49 or grade D is the failing line. Next, the teacher may inspect more beyond the passing rate. A higher resolution of how the grade distributed in the class is wanted. The function IF is applicable here but a little bit messy for grading. You may see IF is no longer a fit candidate in the following illustration.

E2 | =IF(D2>=90,"A",IF(D2>=80,"B",IF(D2>=70,"C",IF(D2>=50,"D","F"))))

IF works best only if there are a few logic operations involved. The readability is terrible for this case. The missing bracket for all the IF statements is a common mistake. Besides, the user may get lost when tackling such a complicated (nested) syntax. It is also a headache to make an amendment when there’s a possible change in the evaluation system in the future.

C Better Solution to Grade by VLOOKUP

IF is a nightmare in this case. Fortunately, VLOOKUP is another powerful alternative in Excel to handle such judging patterns. Some preparation is needed before using this function.

1) Creating a Grade Reference Table

First, let’s create a new workbook called gradeTable and type all the cut-off scores in this separated page. Please note that the scores must enter in ascending order as required by Excel. It is good to keep a workbook for one purpose only. For example, in case there’s a new name “Excellence” instead of “A” for those who reached 90, I can modify the grade table inside its exclusive workbook and all the dependent syntaxes would be updated by themselves.

2) VLOOKUP functions

Then, let’s replace the scaring IF syntax into the nice VLOOKUP function.

E2 | =VLOOKUP(D2,gradeTable!$A$2:$B$6,2,TRUE)
E6 | =VLOOKUP(D6,gradeTable!$A$2:$B$6,2,TRUE)

There’re four major parts in the VLOOKUP syntax. Take cell E2 as an example in Excel on iPad,

Cells to look at, where I set D2
The criteria table. Bridging with an exclamation mark, Excel can retrieve the grade table from another workbook. However, please do not include the header of the table.
Which column of value to be returned if the inspecting cell is matched. Starting from the leftmost vertical column (score), the second column (grade) carries the letter grade we want Excel to show once the score is close or equal to the criterion in the same horizontal row.
Approximate or exact matching. We set TRUE for the most approximate from the next criterion.

C2 Remark on the reference table

When copying the syntax to other rows by AutoFill, it is important to cite a fixed range for the criteria table. One way to make a permanent address can be achieved by Absolute Reference.

When generating similar syntaxes across series, adding a dollar sign ($) before the row or the column would lock that reference. Here is showing the comparison for auto-filling from the left to the right column with $ and not. The relationship between the autofill cells and their origin is clear by tracing precedent.

The cited value can be changed dynamically as long as the connection is established and can be shared across files and documents. Nonetheless, it is quite complicated to entangle here and extend to other Office apps, like Word. Thus, I have an alternative feature to replace the reference placed in the existing VLOOKUP.

Define Name for the frequently cited cell(s)

There’s always a better option to achieve the same goals. May I introduce you the Define Name under the tab Formulas. Selected the same range, we can assign a more intuitive name. The next time I revisit this sheet, I can readily understand the purpose of the citing range. Putting it in another way, a Name is one reference representing for another reference.

To reuse the range by defined name is simple, we can directly type the name as what we used to type the function name. When we need to expand the grade table for an even complicated case, we can plainly revise the definition of the range effortlessly. Also, another remark on the screenshot above is that Formula Builder is suitable when using an unfamiliar function. Just click the “fx” icon at the formula input bar to recruit it.

E2 | =VLOOKUP(D2,gradeTable!$A$2:$B$6,2,TRUE)
E2 | =VLOOKUP(D2,gradeTable,2,TRUE)

Reuse any cell(s)

Not only range but also any single variable can be named. Recall the simple IF function I explained, in the beginning, to determine whether one is passed or not. We can prior-define the cut-off for passing by the same Define name feature.

The original command is an example of hard-coding as it imprinted a fixed constant in the formula. If 60 is the new cut-off for next academic year, the only task to update the cell B2 in sheet scoreToPass.

E2 before | =IF(D2>=50,"pass","fail")
E2 after  | =IF(D2>scorePassCutOff,"pass","fail")

Today’s convenience might become tomorrow’s inconvenience”.

D Absentees and other cases in the test

How about the test records starting from 6? Some test takers have skipped the test as remarked as “ABS” for absent. The school would retrieve an 80% discounted score from the homework score approved absentee. Besides, the invigilator found a cheater in the test. The school would mark 0% of that test as punishment. The teacher now unhides the registry office remarks in this sheet.

In CSV format, the teacher also requests a student homework record from the registry office.

After prep-up, let’s continue on writing the case condition syntax. The function SWITCH is a painkiller in such a complicated case. Similar to VLOOKUP, we can already have another sheet contains all the special case and their action on test score adjustment. We can define the score adjustment percentage to reuse it later.

SWITCH is also intuitive though it looks like a monster. The structure is to first tell Excel what cell value to look at. Then the function returns the corresponding output for each matched labels, otherwise return the default value. In other words, there’re 3 parts in the SWITCH function. In the following code snippet, I break each part for easier understanding.

F2 | =SWITCH(E2,"med",PRODUCT(medReasonMarkAdjustHomework,VLOOKUP(A2,societyHomeWork.csv!societyHomework,2,FALSE)),"cheat",PRODUCT(D6,cheatReasonMarkAdjustTest),D2)A  | =SWITCH(E2,
B1 |         "cheat",PRODUCT(D6,cheatReasonMarkAdjustTest),
B2 |         "med",PRODUCT(medReasonMarkAdjustHomework,VLOOKUP(A2,societyHomeWork.csv!societyHomework,2,FALSE)),
C  |         D2)

A directs Excel to look at E2 for cases
B guides Excel to return the value if the label of E2 is matched
C dictates Excel can just quote the raw score (testSociety) as there’s no problem

For the second piece of this SWITCH function, we assigned what answer should Excel replied for each case code.

B1| "cheat",PRODUCT(
  |           D6,
  |           cheatReasonMarkAdjustTest
  |           )

If subjects are “cheat” (B1), then their final test score would multiply the variable cheatReasonMarkAdjustTest (i.e. 0%) by the function PRODUCT.

B2 | "med",PRODUCT(
   |         medReasonMarkAdjustHomework,
   |         VLOOKUP(A2,societyHomeWork.csv!societyHomework,2,FALSE)
   |      )

On the other hand, if subjects are “med” (B2), then we have to look at their homework score. From the file societyHomeWork.xlsx, we may infer the final test score here from the product of their previous homework quality (i.e. 95) and the adjustment rate for a medical reason (i.e. 80%).

The function PRODUCT involved in the return value can be simplified as an asterisk character, like a*b. Echoing my philosophy on writing human-readable code, I prefer a descriptive function name to declare its purpose and easier to share the code for users other than myself.

E Finding the total number of student passed

Finally, we are above to reach the end of this article. Since the letter grade has been, calculated by Excel, the teacher can now know the passing rate of the Society test.

1) Blending the function COUNTIF and COUNT

The leading COUNTIF function reports how many our young learners in one class. It is basically the same as the COUNT function in the tail. The difference is just with the extra criterion for Excel to find any matched cells with denoted by a wildcard character, that is the asterisk (*, same as the multiplication operator).
Add description

Or, we can summon an operator Not Equal To by <> sign that tells Excel to count everything in the range but ruled out any F-involved cells. Both approaches are comparable. They can count any cells that hold the grade F. Again, the operator version can do the same job but shorter.

D7 | =(COUNTIF(D2:D6, "*")-COUNTIF(D2:D6,"F"))/COUNT(B2:B6)
D7 | =COUNTIF(D2:D6,"<>F"))/COUNT(B2:B6)

The syntax produced 0.6 in the cell D7. We may change the number format and round into Percentage at 60%.

Conclusion: Interchangeable means for the same goals

There is a rich choice provided to finish the job. It is so interesting to compare these solutions. Just like in psychological research on how human handling stress, coping. The problem can be solved more effective if there’s a match between the method and stressor. IF works best for a small piece of logical operation while VLOOKUP is a smarter sibling for a bigger issue. Although some aforementioned Excel features are generic, they provide a better scheme for later data manipulation. Always consider how flexible the syntax before writing it.

Human and machine-readable

Literacy in information technology is important in the present changing world. The machine-readable data granted by the registry office enables the teacher to manipulate the data for another task needed. Imagine another office clerk, supplies the same “data” by a screenshot or summarised grade instead of the raw score (Well, they are “digital” file). Both materials are needed to manual modification before it can feed the program.

Do you like this article?

If yes, hit the like button or comment below. Next week, I will take a break of Excel series and turn into the presentation skills, like script-making by Word and slide-designing by PowerPoint. Stay tuned