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.

=IF(condition,value_if_true,value_if_false)

Let’s look at a simple example:

https://go.ezodn.com/ads/charity/proxy?p_id=e87521e5-5fe9-44d2-45c9-203ed7a750bc&d_id=256322&imp_id=2838110288663067&c_id=1081&l_id=10016&url=https%3A%2F%2Fsecure.givelively.org%2Fdonate%2Famazon-conservation-association%2Ftake-action-for-the-amazon-all-gifts-matched-36e1afc9-58be-4332-b550-3111a4f60e62&ffid=1&co=CN

1Hello2World
=IF(A1="Hello","World",)

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:

1Empty
=IF(LEN(A1),"Not Empty","Empty")2FALSEIs 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:

1100Even
=IF(ISNUMBER(A1),IF(ISODD(A1),"Odd","Even"),"Not a number")2HelloNot 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:

=IF(AND(ISNUMBER(A1),ISODD(A1)),"Odd",)

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.

=IF(ISNUMBER(A1), IF(ISODD(A1),"Odd","Even"), "Text")

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.

About

Welcome to ScriptEverything.com! On this website you'll find things I've learned while tinkering with code and fiddling around with apps.
I enjoy programming with Python and Javascript, and I tango daily with data and spreadsheets in my regular line of work.
When I'm not behind a computer or at work, you'll find me wandering through the bush with my kids getting lost geocaching.

report this ad

Copyright ??2022 > script everything

x

無法連接到 reCAPTCHA 服務(wù)。請(qǐng)檢查您的互聯(lián)網(wǎng)連接，然后重新加載網(wǎng)頁以獲取 reCAPTCHA 驗(yàn)證。

IF Formula With Multiple Conditions: Google Sheets – > script everything