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”.
Syntax of IF function in Google Sheets:
IF(logical_expression, value_if_true, value_if_false)
- logical_expression – This is the condition that you check in the function. It is an expression or reference to a cell containing an expression that would return a logical value, i.e. TRUE or FALSE.
- value_if_true – The value the function returns if logical_expression is TRUE.
- value_if_false – (optional argument) The value the function returns if logical_expression is FALSE. If you don’t specify the value_if_false argument and the checked condition is not met, the function would return FALSE.
Now let's look at some examples to understand how to use IF function in Google Sheets in real life scenarios.
Example 1: Checking a Single Condition With If Function
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.
Example 2: Checking Multiple Conditions With If Function
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.
Example 3: Calculating Commissions Using IF Function in Google Sheets
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.
Example 4: Using AND/OR Operators in IF Function
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”.
You May Also like the following Tutorials:
- Google Sheets Help – IF Function.
- How to Use IF Function in Excel.
- How to Sort Data in Google Sheets.
- How to Quickly Split Text in Google Sheets.
- Using COUNTIF Function in Google Sheets.
- Using IFS Function in Google Sheets.
- Using IFERROR function in Google Sheets.
- Using OR function in Google Sheets.