The IF function in Google Sheets is one of the easiest functions to learn, and while this holds true, it is also a very helpful one. Show
In this tutorial, I invite you to take a closer look at how Google Spreadsheet IF function works and what advantages you will get from using it. What is the IF function in Google Sheets?Whenever you use the IF function, you create a decision tree in which certain action follows under one condition, and if that condition is not met – another action follows. For this purpose, the condition of the function must be in a format of the alternative question with only two possible answers: "yes" and "no". This is what a decision tree may look like:
So, the IF function allows you to ask a question and indicate two alternative actions depending on the received answer. This question and the alternative actions are known as three arguments of the function. IF function syntax in Google SheetsThe syntax for the IF function and its arguments are as follows: =IF(logical_expression, value_if_true, value_if_false)
Let's explore the arguments of our IF function in more detail. The first argument represents a logical question. Google Sheets answers this question with either "yes" or "no", i.e. "true" or "false". How to formulate the question properly, you may wonder? To do that, you can write a logical expression using such helpful symbols (or comparison operators) as "=", ">", "<", ">=", "<=", "<>". Let us try and ask such a question together. Usage of the IF functionLet's assume that you are working in the company selling chocolate in several consumer regions with many clients. This is what your sales data may look like in Google Sheets:
Imagine that you need to separate sales made in your local regions from those from abroad. To accomplish that, you should add another descriptive field for each sale – a country where the sales took place. Since there is a lot of data, you need this description field to be created automatically for each entry. And this is when the IF function comes to play. Let's add the "Country" column to the data table. "West" region represents local sales (Our Country), while the rest are the sales from abroad (Rest of the World). How to write out the function properly?Place the cursor in F2 to make the cell active and type in the equality sign (=). Google Sheets will immediately understand that you are going to enter a formula. That's why right after you type the letter "i" it will prompt you to choose a function that begins with that same letter. And you should choose "IF".
After that, all your actions will be accompanied by prompts as well. For the first argument of the IF function, enter B2="West". As with the other Google Sheets functions, you don't need to enter the address of the cell manually – a mouse click is enough. Then enter comma (,) and specify the second argument. The second argument is a value that F2 will return if the condition is met. In this case, it will be the text "Our Country". And again, after the comma, write the value of the 3rd argument. F2 will return this value if the condition is not met: "Rest of the World". Do not forget to finish your formula entry by closing parenthesis ")" and pressing "Enter". Your entire formula should look like this: If everything is correct, F2 will return the text "Our Country":
Now, all you have to do is to copy this function down column F. Tip. There's one way to process the entire column with one formula. The ARRAYFORMULA function will help you do that. Using it in the first cell of the column, you can test all cells below against the same condition, and return the corresponding result to each row at the same time:
Let's examine the other ways of working with the IF function. IF function and text valuesThe usage of the IF function with a text has already been illustrated in the example above. Note. If the text is being used as the argument, then it must be enclosed in double-quotes. IF function and numerical valuesYou can use numbers for the arguments just as you did with the text. However, what is very important here is that the IF function makes it possible to not only fill cells with certain numbers based on the conditions met but also calculate. For example, let's say you offer your clients various discounts based on the total value of the purchase. If the total is more than 200, then the client gets a 10% discount. For that, you need to use column G and name it "Discount". Then enter the IF function in G2, and the second argument will be represented by the formula that calculates the discount:
IF blanks/non-blanksThere are cases when your result depends on whether the cell is empty or not. There are two ways to check that:
IF in combination with other functionsAs you have already learned, the text, numbers, and formulas can act as the arguments of the IF function. However, other functions can play that role as well. Let's see how it works. Google Sheets IF ORRemember the first way you figured out the country where you sold chocolate? You checked if B2 contained "West". However, you can build the logic the other way around: list all the possible regions that belong to the "Rest of the World" and check if at least one of them appears in the cell. The OR function in the first argument will help you do that: =OR(logical_expression1, [logical_expression2, ...])
As you can see, you just enter as many logical expressions as you need to check and the function searches if one of them is true. To apply this knowledge to the table with sales, mention all the regions that belong to the sales abroad, and the other sales will automatically become local:
Google Sheets IF ANDThe AND function is just as simple. The only difference is that it checks if all listed logical expressions are true: =AND(logical_expression1, [logical_expression2, ...]) E.g. you need to narrow the search to your town and you know that it is currently buying only hazelnuts. So there are two conditions to consider: region – "West" and product – "Chocolate Hazelnut": Nested IF formula vs. IFS function for Google SheetsYou can also use the IF function itself as an argument for the bigger IF function. Let's assume that you have set stricter discount conditions for your clients. If the total purchase is more than 200 units, they get a 10% discount; if the total purchase is between 100 and 199, the discount is 5%. If the total purchase is lower than 100, there is no discount whatsoever. The following formula shows how the function will look in the cell G2:
Note that it is another IF function that is used as the second argument. In such cases, the decision tree is as follows:
Let's make it even more fun and complicate the task. Imagine that you're offering the discounted price to the one region only - "East". To do that correctly, add the logical expression "AND" to our function. The formula then will look the following way:
As you can see, the number of discounts has reduced greatly while their amount remains intact. There's also an easier way to write the above thanks to the IFS function: =IFS(condition1, value1, [condition2, value2, …])
Here's how the above formula will look with IFS:
Tip. If there's no true condition, the formula will return the #N/A error. To avoid that, wrap your formula with IFERROR: SWITCH as an alternative to multiple IFsThere's one more function you may want to consider instead of the nested IF: Google Sheets SWITCH. It checks if your expression corresponds to a list of cases, one by one. When it does, the function returns a corresponding value. =SWITCH(expression, case1, value1, [case2, value2, ...], [default])
Here are a couple of examples. To test your cells against a text, use ranges as an expression:
In this formula, SWITCH checks what record is in every cell in column B. If it's West, the formula says Our Country, otherwise, Rest of the World. ArrayFormula makes it possible to process the entire column at once. To work with calculations, it's better to use a boolean expression:
Here SWITCH checks if the result of the equation is TRUE or FALSE. When it's TRUE (like if E2 is really greater than 200), I get a corresponding result. If none of the cases in the list is TRUE (meaning they are FALSE), the formula simply returns 0. Note. SWITCH doesn't know how to calculate the entire range at once, so no ARRAYFORMULA in this case. IF statements based on a countOne of the questions we get asked a lot is how to create the IF formula that will return whatever you need if the column contains or doesn't contain a certain record. For example, check if a customer's name appears more than once in a list (column A) and put the corresponding word (yes/no) into a cell. A solution is simpler than you may think. You need to introduce the COUNTIF function to your IF:
Make Google Sheets build IF formulas for you – IF Formula Builder add-onIf you're tired of keeping track of all those extra characters and proper syntax in formulas, there's another solution available. IF Formula Builder add-on for Google Sheets offers a visual way of creating IF statements. The tool will handle syntax, extra functions and all required characters for you. All you need to do is:
As you can see, each logical expression takes its own line. The same goes for true/false outcomes. This reduces the number of possible confusion over the formula drastically. As you fill everything out, the formula for use will grow in the preview area at the top of the window. To its left, you can select a cell in your sheet where you'd like to have the formula. When you're ready, paste the formula into the cell of interest by clicking the Insert formula button at the bottom. Please visit the online tutorial for IF Formula Builder to see all options described in detail. I hope that there's no room for any doubt now that the IF function, though a very simple one at first glance, opens the door to many options for data processing in Google Sheets. But if you still have questions, feel free to ask them in the comments section down below – we'll be happy to help! You may also be interested inBagaimana cara merumuskan formula spreadsheet?Membuat rumus sederhana di Excel. Di lembar kerja, klik sel tempat Anda ingin memasukkan rumus.. Ketik tanda = (sama dengan) diikuti oleh konstanta dan operator (hingga 8192 karakter) yang ingin Anda gunakan dalam perhitungan. Untuk contoh kami, ketik =1+1. Catatan: ... . Tekan Enter (Windows) atau Return (Mac).. Rumus formula apa saja yang biasa anda gunakan di MS Excel Google sheet?Berikut daftar rumus Google Sheets yang paling dibutuhkan:. SUM. Pertama adalah rumus SUM yang berguna untuk melakukan penjumlahan. ... . 2. AVERAGE. Selain penjumlahan, rumus umum lainnya yang pasti Anda butuhkan adalah mencari rata-rata dari sebuah data. ... . 3. COUNT. ... . MAX. ... . MIN. ... . TRIM. ... . PROPER. ... . 3. GOOGLETRANSLATE.. Apa fungsi dari Google Spreadsheet?Spreadsheet Google mendukung formula sel yang biasa ditemukan di sebagian besar paket spreadsheet desktop. Formula tersebut dapat digunakan untuk membuat fungsi yang melakukan pengolahan data dan kalkulasi string dan angka.
|