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 statement`IF( 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 this`IF(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`IF`

function. 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