IF statements generally is a little intimidating, however they’re one of the vital helpful features in spreadsheet applications. They permit you to carry out calculations primarily based on whether or not or not your uncooked information meets sure circumstances. The IF operate might be mixed with many different features to construct formulation that suit your actual necessities, too.
That’s all properly and good as soon as you realize what you’re doing, however generally the training course of might be the true problem. Fortunate for you, we’ve received every little thing you want right here on this article to grasp the fundamentals of the IF operate in Google Sheets.
What Does the IF Operate Do?
The Google Sheets variant works equally to Microsoft Excel IF features. Mainly, it calculates primarily based on whether or not or not a worth from a variety is returned as TRUE or FALSE for specified standards.
For instance, you might use an IF operate to place “Move” right into a cell if an individual acquired above a sure grade. As in, it’s TRUE that the grade is above the desired share.
Let’s check out how the syntax works, then we’ll dive into precisely methods to do the above instance and some others. The examples are going to be simplified, in order that they’re straightforward to comply with. However, you may apply the identical concepts to a lot bigger and extra complicated spreadsheets.
Google Sheets IF Operate Syntax
The syntax of a operate in a spreadsheet program like Google Sheets is the bones of the way it works. You possibly can consider it form of like an algebraic expression earlier than you add the values in.
The syntax for the Google Sheets IF operate goes as follows:
IF(logical_expression, value_if_true, value_if_false)
Right here’s what every a part of the syntax means:
- IF tells Google Sheets which operate you need to use.
- Logical_expression is the situation you need the IF operate to test in opposition to. It should be capable to return a TRUE or FALSE assertion.
- Value_if_true is the worth to return if the situation within the logical expression is met.
- Value_if_false is an non-compulsory parameter that tells Google Sheets what worth to return if the situation isn’t met. If no worth is specified, it should return FALSE.
Instance 1: Making a Easy Formulation With the IF Operate in Google Sheets
The only means to make use of the IF operate is by itself with numerical values. So, let’s first have a look at such an instance. Let’s fake we’re the supervisor at a warehouse, and we’ve needed to prepare our workers in a brand new office security program that staff legally have to move to proceed working.
Contemplating the beneath information set the place a passing mark can be 50/100 we might use the system:
=IF(B2>=50,"Move","Fail")
You then would simply need to click on and drag the small blue field within the right-hand nook over the remainder of the column to use the system to all of the cells. Right here’s how the system is utilized inside the syntax.
B2>=50, is the logical_expression to indicate the situation that have to be met. As >= is the operator in Google Sheets for better than or equal to, the logical expression in easy phrases is: If B2 is bigger than or equal to 50.
“Move”,”Fail” tells Google Sheets what to return if the logical_expression is TRUE or FALSE, respectively. On this instance, anybody that has a check mark that’s better than or equal to 50 has Move subsequent to their title. These which can be beneath 50 have Fail.
Instance 2: Nested IF Capabilities in Google Sheets
Nested IF features are ones that carry out a second IF operate inside the identical system. They basically ask for a second situation if the primary one returns a FALSE assertion.
Let’s check out the identical information set as earlier than to make sense of this. This time, we’re checking whether or not the staff are eligible for an additional shot on the check. Everybody that handed doesn’t have to take the check once more, and people who received lower than 40% should do some additional coaching first.
As you may see, though Pete T failed the check, he nonetheless has No beneath the retraining column as his mark was beneath the 40% minimal to retake the check. John W, then again, receives a Sure worth.
Let’s have a look beneath the hood of the system once more:
- B2>=50 is similar as earlier than and is the logical_expression of the “nest” IF operate
- “No” is the worth to return if the situation is met. I.e., these with a mark better than or equal to 50 don’t need to retake the check.
- IF(B2<40, is the value_if_false for the primary if operate. It tells Google Sheets to carry out a second if operate if the worth isn’t better than or equal to 50. It now checks if the worth is lower than 40.
- “No”,”Sure” are the value_if_true and value_if_false for the nested IF operate.
Here’s a circulate chart of how this calculation works for these of you which can be nonetheless a bit of confused.
Instance 3: Combining the IF Operate With Different Capabilities
There are tons of the way to make use of the IF operate with others to construct particular formulation to your wants. One of many extra frequent ones is the AND operate. Doing this can permit you to test a number of standards earlier than returning a outcome.
We’ll use the identical information once more, however this time you might want to choose an individual to fireplace. Let’s say it must be the individual that failed the check AND is ineligible to retake the check.
We might use the next mixture of the AND and IF features to find out this.
=IF(AND(D2="No",C2="Fail"),"Sure","No")
The outcome can be to fireplace Pete T.
Let’s break down how the system labored: The AND operate is nested contained in the IF operate and works because the logical_expression. The remainder of the operate works the identical as the opposite IF features we’ve mentioned already. So, the system says, IF D2=”No” AND C2=”Fail” are TRUE, present “Sure” if the result’s FALSE return “No”
There are additionally loads of different IF features that work with a secondary operate, akin to COUNTIF in Google Sheets. These features ought to be straightforward to overcome when you wrap your head round customary IF features.
IF Capabilities 101
IF features can actually ramp up your spreadsheet recreation and work properly inside different features. Now that you just’ve adopted together with our examples, we suggest you spend a while working towards what you’ve discovered. See for those who can give you a few of your individual logical_expression and value_if_true mixtures in a few of your individual worksheets.
Learn Subsequent
About The Creator