Download ArticleDownload ArticleThis article was co-authored by How.com.vn staff writer, Nicole Levine, MFA. Nicole Levine is a Technology Writer and Editor for How.com.vn. She has more than 20 years of experience creating technical documentation and leading support teams at major web hosting and software companies. Nicole also holds an MFA in Creative Writing from Portland State University and teaches composition, fiction-writing, and zine-making at various institutions.
This article has been fact-checked, ensuring the accuracy of any cited facts and confirming the authority of its sources.
This article has been viewed 109,941 times.
Learn more...
One of the most useful features of Microsoft Excel is the IF-THEN statement, which is used to perform calculations or display text based on whether a condition is true. If you're confused about the IF function, this handy How.com.vn tutorial will guide you through writing useful IF-THEN formulas, from making simple comparisons to testing multiple conditions. Read on for examples, troubleshooting tips, and more!
Steps
Simple Examples
Download Article
=IF(B3>C3, "Goal Met", "Goal Not Met").[2]- In this example, the condition is B3>C3, meaning "If the value of B3 is greater than C3."
- If the value of B3 is greater than C3, then Goal Met will appear in the cell.
- If the value of B3 is less than the value of C3, then Goal Not Met will appear in the cell.
=IF(B3=C3, "Goal Met").- In this example, the condition is B3=C3, meaning, "If the value of B3 is equal to the value of C3."
- If the values are equal, "Goal Met" will appear in the cell.
- If the values are not equal, a zero will appear in the field because we did not define a third (ELSE) value.[3]
=IF(B3*2>C3, C3*400, "Good").- In this example the condition is B3*2>C3, meaning "If the value of B3 multiplied by 2 is greater than the value of C3."
- If the value of B3*2 is greater than the value of C3, the value of C3 multiplied by 400 will appear in this cell.
- If the value of B3*2 is not greater than the value of C3, the word "Good" will appear in the cell.
=IF(B3="Sold", "1", "")- In this example, the condition is B3="Sold", meaning "If the value of cell B3 contains the word Sold."
- If B3 contains the word "Sold," a "1" will appear in the cell.
- If B3 says anything other than the word "Sold," the condition is false. Since our ELSE value is " ", which is two double quote marks with nothing in between, the cell value will be blank.
=IF(D3="Taxed", F3*.07, "0").- In this example, the condition is D3="Taxed", meaning "If the value of D3 is the word Taxed."
- If D3 contains the word "Taxed," the result will be the value of F3 multiplied by .07.
- If D3 contains anything other than the word "Taxed," the result will be 0.
Things to remember.- An IF statement can have two different results—one result if the condition is true, and another result if the condition is false.[4]
- The ELSE value, which is what will happen if the result is false, is optional—if you don't specify what to place in the cell if the condition is false, a "0" will appear in the cell. If you'd rather the cell be blank, make your ELSE value "".
- When referring to specific text in a condition or THEN/ELSE value, always surround that text in quotation marks.
Testing Multiple Conditions
Download Article
You can nest up to 64 IF/THEN statements in a single formula. For example, let's say you want to create a formula that calculates sales tax for purchases made from Alaska, California, Oregon, and Washington. Since each of these four states has its own tax rate, we'll need to create a formula that contains four IF/THEN statements to test four conditions. Assuming the sale amount is in G1 and the purchaser's state is in F1, our formula would look like this, using the 2022 tax rates for these 4 states:- =IF(F1="Alaska", G1*0,IF(F1="California",G1*0.0725,IF(F1="Oregon",G1*0,IF(F1="Washington", G1*0.065))))
Nesting many IF/THEN statements can be challenging. Aside from the very long formulas, creating very long IF/THEN statements may become cumbersome if you have to frequently change them. In our previous example, we'd need to update our IF/THEN statements any time a state's tax rate changed.[5]- A good alternative to IF/THEN statements in this situation would be to add the values to a single table and use a VLOOKUP formula to query the table for the proper rates.
- For example, if we add a list of our states to column A and their corresponding tax rates in column B, we could write a VLOOKUP formula that does the math for us without specifying the amounts in the formula itself. The shortened version of the above nested IF/THEN formula would be =G1*VLOOKUP(F1,A:B,2,TRUE).
- If you had to update a tax rate, you'd now only have to update it in the referenced table, not all of your formulas.
Troubleshooting
Download Article
The result is zero (0). This means that you didn't specify a value for either the value_if_true or value_if_false arguments.[6]- If you don't specify a value_if_false value, the result will always be 0 if the condition is false.
The result is the #NAME? error. This indicates that a syntax error—make sure you've surrounded the formula after = with parentheses, your arguments are separated by commas, and that any plain text is surrounded by quotation marks.[7]- For example, this IF/THEN statement will result in the #NAME? error because there are no quotation marks around the value OK: =IF(A2>1,OK).
- The correct syntax would be =IF(A2>1,"OK")
Expert Q&A
Video
Tips
References
- ↑ https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
- ↑ https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
- ↑ https://support.microsoft.com/en-us/office/if-function-nested-formulas-and-avoiding-pitfalls-0b22ff44-f149-44ba-aeb5-4ef99da241c8
- ↑ https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
- ↑ https://support.microsoft.com/en-us/office/if-function-nested-formulas-and-avoiding-pitfalls-0b22ff44-f149-44ba-aeb5-4ef99da241c8
- ↑ https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
- ↑ https://support.microsoft.com/en-us/office/how-to-correct-a-name-error-b6d54e31-a743-4d7d-9b61-40002a7b4286
About This Article
1.The syntax for an IF-THEN statement is =IF(logical_test, value_if_true, value_if_false).
2."logical_test" is the condition you want to test, such as whether a cell value is greater than or equal to another cell.
3."value_if_true" is what the formula should do if the test condition is true.
4."value_if_false" is what the formula should do if the test condition is "not" true.
Is this article up to date?
⚠️ Disclaimer:
Content from Wiki How English language website. Text is available under the Creative Commons Attribution-Share Alike License; additional terms may apply.
Wiki How does not encourage the violation of any laws, and cannot be responsible for any violations of such laws, should you link to this domain, or use, reproduce, or republish the information contained herein.
- - A few of these subjects are frequently censored by educational, governmental, corporate, parental and other filtering schemes.
- - Some articles may contain names, images, artworks or descriptions of events that some cultures restrict access to
- - Please note: Wiki How does not give you opinion about the law, or advice about medical. If you need specific advice (for example, medical, legal, financial or risk management), please seek a professional who is licensed or knowledgeable in that area.
- - Readers should not judge the importance of topics based on their coverage on Wiki How, nor think a topic is important just because it is the subject of a Wiki article.
