Today, I will show you how to make a scorecard in Excel and use the IF Function to calculate the scores automatically. We have a list of students who have all completed our test. If the student has scored more than 80 points, we should fill the results column with “Pass”; if they score less than 80 points, we should fill it in with “Fail”.
What is the IF Function
The IF function checks if a value in a cell is True; if it is, do one thing, and if it isn’t, do something else.
Let’s say we have a class test and need to see if the students passed the test. To pass this test, the student needs to score over 80 points.
Our table might look something like this:
Score Card
| Student | Score | Results |
|---|---|---|
| Janice | 80 | Pass |
| Elliott | 50 | Fail |
Wouldn’t it be nice if the results column were to automatically update as you entered the points next to the name? That is something we can do with the IF function.
How to Write the IF Function in Excel
In Excel, we write the IF function like this.IF(B2 >= 80 ,"Pass","Fail")
To write an IF function In Excel, you need to start by typing the equals sign (=) in the cell where you want to write the formula. Then, write the function itself by including IF(). You will need to specify your conditions within the brackets.
I will use a numbered list so that you can see each part of the statementIF( 1 , 2 , 3)
The IF function has three parameters.
- The condition we are looking for.
- What should be done if that condition is true?
- What to do if it is False.
In our example:
IF(B2 > 80 ,"Pass","Fail")IF( 1 , 2 , 3)
- Is cell B2 greater than 80
- If True, type “Pass”
- If False, type “Fail”
Logical Operators
Our IF function uses a logical Operator. You will need one of these in your IF function to compare the data.
| Logical Operator | Definition | Example |
|---|---|---|
| > | GreaterThan | 8 > 3 |
| < | Less Than | 3 < 4 |
| = | Equal To | 5 = 5 |
| >= | Greater Than or Equal To | 7 >= 6 or 7 >= 7 |
| <= | Less Than or Equal To | 3 <= 6 |
| <> | Not Equal To | 8 <> 7 |
Building Our IF Function
We need to see if the student scores over 80 points on our test.
Where to Check
This will work great if the student scores 81 points or more, but if they score exactly 80, the IF function will mark the result as false. Why? We told Excel to look for something greater than (>) 80. As 80 equals 80 and not more, it is marked as false. So we will need to change our parameter to this
- Cell >= 80
This will check to see if the score is 80 or more.
IF True
Next, we must tell the IF Function what to do if it is True.
- “Pass”
We write text in double quotes to let Excel know that we want it to fill in the cell with text; if you want it to fill the cell with a number, you don’t use the speech marks.
IF False
Only some of our students will score above 80 points; we must tell Excel what to write if that happens.
- “Fail”
This is done the same way as telling our Excel what to do if it is true.
Putting it all together
Our function now looks like thisIF(B2 >= 80, "Pass", "Fail")
Other Uses for IF Function
Homework Received
IF(C2<>"","Received","Pending")
In the Homework Received table above, we use the IF function to check the Data Received column to see if there is any data in the cell. It will change the Status to Received if there is one. If there is no data in the cell, it will say Pending
We check whether the cell is empty using the “Not” logical operator <> and two double quotes"" without a space. Our formula reads, “IF Not Empty, type “Received”. Else, type “Pending.”
Activity Sign-Up Sheet
IF(C2=50, "Paid", "Deposit")
We use the IF function to update the Status column automatically. To see if the student has paid in full or only a deposit.
Places Available
IF(C2= 10, "Full", "Available")
With this one, we can see if any places are available for the class we want to attend. As you change the number in the Signed Up column, the Status column will update; once the number reaches 10, it will change from “Available” to “Full”.
Nested IF Function
Nested IF is used when you want to check if more than one condition is True.
Sometimes, you need to do more than the two options you get from the standard IF function.
You can replace the 3 in the standard function with another IF.
Standard IF: IF( 1, 2, 3).
Nested IF: IF( 1 , 2 , IF( 1 , 2 , 3))
Places Available II
IF(C2 = 10, "Full", IF(C2 >= 8, "Limited", "Available"))
If there are ten students signed up, type “Full”. If eight or more students are signed up, type “Limited” or “Available.”
Student Grades
IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F"))))
Conclusion
In this post, we have learned about the IF function, how it works, and how to create our own. We have also looked at some sample projects that you can use in your work, such as:
- Student Score Card
- Homework Received Sheet
- Activity Sign-Up Sheet
- Places Available Table
- Places Available II (nested
IF) - Student Grades (nested
IF)
Using the built-in Functions in Excel is an excellent way to boost productivity and complete your work efficiently. There are numerous functions available, and here are a few that I believe are helpful for teachers:
- TRANSFORM: This function converts vertical data to horizontal data and vice versa. You can learn more about it by clicking on this link: Convert Vertical Data Into Horizontal Data in Excel
- SWITCH: This function is similar to the IF function and is a good replacement for the nested
IFfunction. It is easier to write and easier to read. However, it does not work with logical operations. You can only check if the cell contains text or a number. - VLOOKUP: This function searches for a value in the leftmost column of a table and retrieves a corresponding value from a specified column.
- XLOOKUP: This function is a more versatile and powerful version of the VLOOKUP function. It allows you to perform both vertical and horizontal lookups, search for values in multiple columns or rows, and more.
Disclaimer: The data used in this guide is purely fictional and created for illustrative purposes only.
Leave a Reply