[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
- \n
- logical_expression<\/b> \u2013 the expression to evaluate as TRUE<\/b> or FALSE<\/b>.<\/li>\n
- value_if_true<\/b> \u2013 the value that the function returns if logical_expression <\/b>is TRUE. This can be a number, text, or even another formula.<\/li>\n
- value_if_false<\/b> \u2013\u00a0 the value that the function returns if logical_expression <\/b>is FALSE. This can be a number, text, or even another formula. If not included, it will return a blank value.<\/li>\n<\/ul>\n
This flowchart explains the internal logic of the IF function.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IF-function-1.png” alt=”IF function 1″ title_text=”IF function 1″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”how” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
- <\/ul>\n
- AND evaluates multiple criteria and returns TRUE if all of them are TRUE.<\/li>\n
- OR evaluates multiple criteria and returns TRUE if one of them is TRUE.<\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
IF with AND function<\/h5>\n
Here\u2019s an example of an IF function that also uses an AND function.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=IF(AND(B2>19, B2<24), \u201dEarly 20\u2019s\u201d, IF(AND(B2>23, B2<27), \u201dMid 20\u2019s\u201d, IF(AND(B2>26, B2<30), \u201dLate 20\u2019s\u201d, \u201dNot in their 20\u2019s\u201d)))<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IF-function-7.png” alt=”IF function 7″ title_text=”IF function 7″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
The AND function enables testing against two criteria in my logical test: a minimum value and a maximum value. By doing so, I can separate the ages into segments: \u201cEarly 20s\u201d, \u201cMid 20s\u201d, and \u201cLate 20s\u201d.<\/p>\n
Notice that I\u2019ve also included \u201cNot in their 20s\u201d as the value_if_false at the end of the formula. This ensures that data that doesn\u2019t fit the established criteria (ages not in the 20s) will still have an output that makes sense.<\/p>\n
Check out our post on the AND function<\/a> for a complete overview including other use cases.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
IF with OR function<\/h5>\n
I\u2019ll now show an example using the OR function within an IF statement. This evaluates the overall experience of workers: anyone above the age of 25 or with more than 4 years of work experience, is considered \u201cexperienced\u201d.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=IF(OR(B2>25, C2>4), \u201cExperienced\u201d, \u201cNot experienced\u201d)<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IF-function-8.png” alt=”IF function 8″ title_text=”IF function 8″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Note the difference between this example and the previous one. Here, I want the value to be TRUE if either criteria is met, so I use the OR function. If I want the value to be TRUE only if all criteria are met, so I used the AND function.<\/p>\n
Check out our article on the OR function<\/a> for a full explanation and more examples.<\/p>\n
[\/et_pb_text][et_pb_text module_id=”ifs” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
IFS Function<\/h2>\n
The IF function helps you apply logical functions to your spreadsheet data through simple TRUE or FALSE evaluations. Nested IFs can be used when you want to test data against multiple criteria, but make formulas hard to read.<\/p>\n
Luckily there\u2019s an alternative to nested IF statements: the IFS function in Google Sheets<\/a>.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
IF follows an else if logic and is one of the most useful Google Sheets functions. You can use it to perform logical tests on your spreadsheets. Allowing you to carry out different actions if the test result is TRUE or FALSE. Essentially, you tell the function: If a cell meets certain criteria, do X, […]<\/p>\n","protected":false},"author":40,"featured_media":27672,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"Among the myriad formulas that Google Sheets provides, probably the IF<\/a> formula is one of the most widely used. It returns a value through an if-then-else logical construct. First, it evaluates the given logical expression. Then Based on whether the evaluation is TRUE or FALSE, the formula returns the first value, or the second respectively. The process is illustrated in the flow chart below.\n\n
![\"IF](\"https:\/\/sheetgo.com\/wp-content\/uploads\/2017\/03\/IF-Formula-Illustration-1-1024x584.png\")
\nSyntax<\/h3>\nIF(logical_expression, value_if_true, value_if_false)<\/strong><\/span>\n
- \n \t
- logical_expression<\/strong><\/span> - an expression or reference to a cell containing an expression that evaluates to either TRUE<\/strong><\/span> or FALSE<\/strong><\/span>.<\/li>\n \t
- value_if_true<\/strong><\/span> - the value that the formula returns if the logical_expression<\/span><\/strong> evaluates to TRUE. This can be a number, text, or even another formula that returns a value. We can also embed another IF formula in here, as part of the nested IF constructs that we will see in a while.<\/li>\n \t
- value_if_false<\/strong><\/span> - this is the value that the IF formula returns if the logical_expression<\/strong><\/span> evaluates to FALSE. Similar to the value_if_true<\/strong><\/span>, this can be a number, text or another formula that returns a value. It can also be another embedded IF formula. Please note that it is an optional input and if we ignore this, we get a blank value in return.<\/li>\n<\/ul>\n
Usage: IF Formula<\/h3>\n
Use case # 1: Regular IF formula constructs<\/h5>\nExamples always help us understand the concepts better. So, I used the following sample data (columns A through E). And, I tried a few common variations of the formula (column F). You will notice that we have experimented with the Boolean values, dates, numbers, and also text.\n\n
![\"IF](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/if1.png\")
\nUse case # 2: Nested IF formula constructs<\/h5>\nThis is a scenario where we embed IF formulas within IF formulas. And we come across this more often than not. We demonstrate it diagrammatically below.\n\n \n\n
![\"IF](\"https:\/\/sheetgo.com\/wp-content\/uploads\/2017\/03\/IF-Formula-Illustration-3.png\")
\n\nAs explained in the above flow chart, we nested an IF within the value_if_false<\/strong><\/span>. Similarly, if need be, we can also nest an IF within the value_if_true<\/span><\/strong>. So, if the Expression-1<\/strong> evaluates to FALSE, the control moves to evaluate the nested IF function. Accordingly, it returns either B or C based on whether the Expression-2<\/strong> evaluates to either TRUE or FALSE respectively.\n\nAlso the diagram is the case for single level nesting. But, we can also try multi-level nesting, i.e. multiple IF formulas placed in a hierarchical fashion. Consider the examples below to understand this concept better.\n\n![\"IF](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/if2.png\")
","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[55,39,28],"class_list":["post-2779","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-ben-collins","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/2779","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=2779"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/2779\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/27672"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=2779"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=2779"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=2779"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} - value_if_true<\/strong><\/span> - the value that the formula returns if the logical_expression<\/span><\/strong> evaluates to TRUE. This can be a number, text, or even another formula that returns a value. We can also embed another IF formula in here, as part of the nested IF constructs that we will see in a while.<\/li>\n \t
- logical_expression<\/strong><\/span> - an expression or reference to a cell containing an expression that evaluates to either TRUE<\/strong><\/span> or FALSE<\/strong><\/span>.<\/li>\n \t
How to use Google Sheets IF function<\/h2>\n
[\/et_pb_text][et_pb_text module_id=”example1″ _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
- <\/ul>\n
Example 1: Regular IF statements<\/h3>\n
The easiest way to understand the IF function is to see it in action.<\/p>\n
Take a look at the following sample data (columns A through E). In column F you can see some common variations of the function.<\/p>\n
You will notice that I have experimented with Boolean values (TRUE or FALSE), dates, numbers, and also text.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IF-function-2.png” alt=”IF function 2″ title_text=”IF function 2″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
There are two rows for each example, to show both results of the function if the value is true or false. To get started, let\u2019s go over the formula in the first two rows.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=IF(E2=\u201dYes\u201d, \u201dOn Campus\u201d, \u201dNot On Campus\u201d)<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In simple terms, I am telling the function \u201cIf the value in cell E2 is \u2018Yes\u2019 the output should be \u2018On Campus\u2019, otherwise it should be \u2018Not On Campus\u2019 \u201c.<\/p>\n
By taking a look at all of the examples, you will get an idea of the versatility of the IF function. You can use it with just about any other function: you can see the DATE function in rows 6 & 7, and the LEN (length of a text string) function in rows 8 & 9.<\/p>\n
It can also use a variety of logical tests to evaluate whether an expression is TRUE or FALSE. The logical tests that you can use can be seen below.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IF-function-3.png” alt=”IF function 3″ title_text=”IF function 3″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”example2″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example 2: IF statements with a calculation as the result<\/h3>\n
You can also use an IF statement to return a mathematical calculation.<\/p>\n
In the following example, I\u2019m evaluating the final grade of a group of students, based on whether or not they receive bonus marks. If they do, their final grade will increase by the percentage indicated, and if they don\u2019t, their final grade will stay the same.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IF-function-4-1.png” alt=”IF function 4″ title_text=”IF function 4″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Notice that in this case, the value_if_true<\/b> is actually a calculation that the function must perform.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=IF(C2=\u201dYes\u201d, B2+B2*D2, B2)<\/p>\n
[\/et_pb_text][et_pb_text module_id=”example3″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example 3: Nested IF statements<\/h3>\n
An IF formula within another IF formula is known as a nested IF statement. You can see the logic in the diagram below.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IF-function-5.png” alt=”IF function 5″ title_text=”IF function 5″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
As this flowchart shows, there\u2019s a nested IF within the value_if_false<\/b>. It could have been nested within the value_if_true<\/b>.<\/p>\n
In my example, if the IF test for Expression-1<\/b> is FALSE, the function goes to the next test: the nested IF function. Accordingly, it returns either B or C based on whether the test for Expression-2<\/b> is TRUE or FALSE.<\/p>\n
This diagram shows a single nested IF statement. But you can also nest multiple IF statements in Google Sheets. This means adding multiple IF functions in a hierarchical fashion.<\/p>\n
The example below uses an IF statement to evaluate how much experience each worker has.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IF-function-6.png” alt=”IF function 6″ title_text=”IF function 6″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=IF(B2=0, \u201dNo experience\u201d, IF(B2<4, \u201dSome experience\u201d, \u201dLots of experience\u201d))<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Again, in simple terms, I\u2019m telling the function \u201cEvaluate if B2 is 0, if it\u2019s TRUE output \u2018No experience\u2019, if it\u2019s FALSE continue to the next IF statement. Evaluate if B2 is less than 4, if it\u2019s TRUE output \u2018Some experience\u2019, if it\u2019s FALSE output \u2018Lots of experience\u2019 \u201d.<\/p>\n
[\/et_pb_text][et_pb_text module_id=”example4″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example 4: IF functions with AND & OR functions<\/h3>\n
The AND and OR logical functions in Google Sheets can be used in combination with the IF function. These two expressions are fairly self-explanatory:<\/p>\n
- \n
- value_if_true<\/b> \u2013 the value that the function returns if logical_expression <\/b>is TRUE. This can be a number, text, or even another formula.<\/li>\n