IF function in Google Sheets can be used when you want to check a condition and then based on it, returns a specified value if it’s TRUE, or else return the other specified value.
For example, suppose you are evaluating marks obtained by students in an exam and you want to know if the student has passed or not. In this case, you can use the IF function and if the score is greater than 35, it would return “Pass” else it would return “Fail”.
IF(logical_expression, value_if_true, value_if_false)
Now let’s look at some examples to understand how to use IF function in Google Sheets in real life scenarios.
Suppose you have the marks of students in an exam and you want to specify whether a student has passed or failed (as shown below).
You can easily find this out by using the IF function in Google Sheets.
Here is the formula that will do this:
It checks whether the score is more than 35 or not. If it is, then it returns Pass, else it returns Fail.
Using the same example as above, what if you have a grading system in school and you have to assign a grade to a student based on the marks obtained.
For example, a student gets F grade if he/she gets less than 35, D for marks between 35 and 50, C for marks between 50 and 70, B for marks between 70 and 90 and A above 90 (as shown below):
In this case, you not only need to check whether the marks are above 35 or not, but also check the range in which it lies.
Here is the formula that will give you the result in this case:
This formula first checks if the score is less than 35 or not. If it is, then it returns F, else it moves on to the next condition, and so on.
Googel Sheets If Function allows you to perform calculations in the value section.
A good example of this is calculating the sales commission for sales rep using the IF function.
In the example below, a sales rep gets no commission if the sales are less than 50K, gets a 4% commission if the sales are between 50-80K and 10% commission if the sales are more than 80K.
Here is the formula used:
In the formula used in the example above, the calculation is done within the IF function itself. When the sales value is between 50-100K, it returns B2*4%, which is the 4% commission based on the sales value.
Within the IF function, you can use the AND/OR operators to check multiple conditions at a time.
For example, suppose you have a list of students with their marks and attendance. Only those students get the scholarship who have a score of greater than 80 and attendance of more than 80%. You can quickly identify such students using the AND condition within the IF function.
Here is the formula that will identify the students eligible for the scholarship.
It works by checking both the conditions in the AND function. If the AND function returns TRUE, the IF function returns “Yes”, else it returns “No”.
Conditional Formatting Based on Another Cell Value in Google Sheets
Creating a Heat Map in Google Sheets (Step-by-Step Tutorial)
How to Split Text to Columns in Google Sheets (with Examples)
How to Convert Excel to Google Sheets (a Step-by-Step Tutorial)
How to Zoom In and Zoom Out in Google Sheets