One of the first formulas I started with when exploring more about the functionality of spreadsheets is the IF formula.
The IF formula is easy to understand and contains only three parameters which are all required. The first parameter is the condition to check, the second is the returned value if the condition is true, and the third parameter is the returned value if the condition evaluates to false.
Let’s look at a simple example:
| A | |
|---|---|
| 1 | Hello |
| 2 | World =IF(A1="Hello","World",) |
Simple demonstration of IF formula
As you can see from this simple example above, the condition (the first parameter of the IF formula) checks if the cell in A1 contains the value "Hello" which, if it does, I want to return the value "World". If it doesn’t contain "Hello" then I don’t want to return anything – this is why the third parameter of my IF formula contains nothing.
Condition Types
The primary purpose of the first parameter is to determine truthiness. This means you can use comparators such as equals or not equals, or greater than or less than another.
Besides using statements of comparison you can also perform truthy checks on the values of cells, for example (IF formulas without a comparator):
| A | B | |
|---|---|---|
| 1 | Empty =IF(LEN(A1),"Not Empty","Empty") | |
| 2 | FALSE | Is FALSE =IF(A2,"Is TRUE","Is False") |
Notice in the first IF formula the condition statement checks if the value in cell A1 has a length. As the result of this statement is 0 this is interpreted as FALSE and therefore will return the value of the third parameter which is a string stating Empty.
In the second IF formula the condition just references the value contained in cell A2 which has just the value FALSE. As this value does not result in TRUE again my third parameter is returned which is a string stating Is False.
While comparative statements in your condition are the means by what most people use IF statements, this highlights that you can use other statements, being aware that if they return 0 or an empty string "" or an empty range the third parameter will be returned as these values are considered FALSE.
Multiple IF Statements
Sometimes when writing an IF statement there are times where you want to continue performing additional checks. Thankfully nesting IF statements is fairly straightforward, as seen in the following example:
Nested IF formulas:
| A | B | |
|---|---|---|
| 1 | 100 | Even =IF(ISNUMBER(A1),IF(ISODD(A1),"Odd","Even"),"Not a number") |
| 2 | Hello | Not a number =IF(ISNUMBER(A1),IF(ISODD(A1),"Odd","Even"),"Not a number") |
In the above examples, I have the same formula in both cell B1 and B2, and they contain multiple IF conditions.
The order when using multiple IF statements is very important, and the way to read these formulas is no different if you were reading text – you start from the left and progress right.
Therefore, in the example, the first condition checked is determining whether the adjacent cell in column A is a number. The result from this formula returns just TRUE or FALSE. In our first case in cell B1 the condition is TRUE therefore we will return the value of the second parameter.
The second parameter happens to be another IF formula where I check if the same cell is an odd value using the formula ISODD, this also just returns TRUE or FALSE. As the value 100 in cell A1 is even the condition will return FALSE and therefore return the third parameter in its IF formula being the string value "Even".
Multiple IF Statement Tips
Reading multiple if statements can get cumbersome once you get beyond having more than 2 nested IF statements. Here a couple of things I have done to help make them more manageable:
AND or OR Formulas
If your multiple IF statements are returning the same value for certain conditions you may want to see if you can refactor the whole statement to use AND or OR formulas, like so:
Insert Line Breaks
You can insert line breaks into your formula which can help you better read your statements. To insert a line break in your code use the Option/Alt + Return keys on your keyboard.
Break Conditions Into Multiple Ranges
Sometimes it’s just easier to break your IF statements up over multiple ranges if you have the room on your sheet.
This can help with debugging your formula to check your IF conditions at each stage are correct. By splitting the values returned from each IF statement into its own range, and then operating on that range with the next IF statement it can be easier to spot where things have gone wrong.
Summary
The IF formula is one of the most common formulas I use when operating in Google Sheets. It’s a very simple formula that contains three parameters with the first being the condition to check, the second being the value to return if the condition is true, and the third being the value to return if the condition is false.
Besides nesting IF formulas based on multiple conditions; check out these other formulas you can use when you have multiple criteria.
If you find your IF statement formulas are becoming too big, you might want to check out the SWITCH statement instead.