How to Use the IF-THEN Function in Excel GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office 178 178 people found this article helpful

How to Use the IF-THEN Function in Excel

Inputting the IF-THEN function in Excel helps with data-based decisions

By Ted French Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. lifewire's editorial guidelines Updated on March 27, 2022 Tweet Share Email Tweet Share Email MS Office Excel Word Powerpoint Outlook

What to Know

The syntax of IF-THEN is =IF(logic test,value if true,value if false).The first argument tells the function what to do if the comparison is true.The second argument tells the function what to do if the comparison is false.
This article explains how to use the IF-THEN function in Excel for Microsoft 365, Excel 2019, 2016, 2013, 2010; Excel for Mac, and Excel Online, as well as a few examples.

Inputting IF-THEN in Excel

The IF-THEN function in Excel is a powerful way to add decision making to your spreadsheets. It tests a condition to see if it's true or false and then carries out a specific set of instructions based on the results. For example, by inputting an IF-THEN in Excel, you can test if a specific cell is greater than 900. If it is, you can make the formula return the text "PERFECT." If it isn't, you can make the formula return "TOO SMALL." There are many conditions you can enter into the IF-THEN formula. The IF-THEN function's syntax includes the name of the function and the function arguments inside of the parenthesis. This is the proper syntax of the IF-THEN function: =IF(logic test,value if true,value if false)
The IF part of the function is the logic test. This is where you use comparison operators to compare two values. The THEN part of the function comes after the first comma and includes two arguments separated by a comma. The first argument tells the function what to do if the comparison is true.The second argument tells the function what to do if the comparison is false.

A Simple IF-THEN Function Example

Before moving on to more complex calculations, let's look at a straightforward example of an IF-THEN statement. Our spreadsheet is set up with cell B2 as $100. We can input the following formula into C2 to indicate whether the value is larger than $1000. =IF(B2>1000,"PERFECT","TOO SMALL") This function has the following arguments: B2>1000 tests whether the value in cell B2 is larger than 1000."PERFECT" returns the word PERFECT in cell C2 if B2 is larger than 1000."TOO SMALL" returns the phrase TOO SMALL in cell C2 if B2 is not larger than 1000. The comparison part of the function can compare only two values. Either of those two values can be: Fixed numberA string of characters (text value)Date or timeFunctions that return any of the values aboveA reference to any other cell in the spreadsheet containing any of the above values The TRUE or FALSE part of the function can also return any of the above. This means that you can make the IF-THEN function very advanced by embedding additional calculations or functions inside of it (see below). When inputting true or false conditions of an IF-THEN statement in Excel, you need to use quotation marks around any text you want to return, unless you're using TRUE and FALSE, which Excel automatically recognizes. Other values and formulas don't require quotation marks.

Inputting Calculations Into the IF-THEN Function

You can embed different calculations for the IF-THEN function to perform, depending on the comparison results. In this example, one calculation is used to calculate the tax owed, depending on the total income in B2. The logic test compares total income in B2 to see if it's greater than $50,000.00. =IF(B2>50000,B2*0.15,B2*0.10) In this example, B2 is not larger than 50,000, so the "value_if_false" condition will calculate and return that result. In this case, that's B2*0.10, which is 4000. The result is placed into cell C2, where the IF-THEN function is inserted, will be 4000. You can also embed calculations into the comparison side of the function. For example, if you want to estimate that taxable income will only be 80% of total income, you could change the above IF-THEN function to the following. =IF(B2*0.8>50000,B2*0.15,B2*0.10) This will perform the calculation on B2 before comparing it to 50,000. Never enter a comma when entering numbers in the thousands. This is because Excel interprets a comma as the end of an argument inside of a function.

Nesting Functions Inside of an IF-THEN Function

You can also embed (or "nest") a function inside of an IF-THEN function. This lets you perform advanced calculations and then compare the actual results to the expected results. In this example, let's say you have a spreadsheet with five students' grades in column B. You could average those grades using the AVERAGE function. Depending on the class average results, you could have cell C2 return either "Excellent!" or "Needs Work." This is how you would input that IF-THEN function: =IF(AVERAGE(B2:B6)>85,"Excellent!","Needs Work") This function returns the text "Excellent!" in cell C2 if the class average is over 85. Otherwise, it returns "Needs Work." As you can see, inputting the IF-THEN function in Excel with embedded calculations or functions allows you to create dynamic and highly functional spreadsheets. FAQ How do I create multiple IF-THEN statements in Excel? Use Nesting in Excel to create multiple IF-THEN statements. Alternatively, use the IFS function.
How many IF statements can you nest in Excel? You can nest up to 7 IF statements within a single IF-THEN statement.
How does conditional formatting work in Excel? With conditional formatting in Excel, you can apply more than one rule to the same data to test for different conditions. Excel first determines if the various rules conflict, and, if so, the program determines which conditional formatting rule to apply to the data.
Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Use the ISBLANK Function in Excel How to Use the Google Spreadsheets AVERAGE Function Use the Excel RIGHT Function to Extract Characters How to Combine the ROUND and SUM Functions in Excel How to Round Numbers Down in Excel With the ROUNDDOWN Function How to Use the Excel DATE Function How to Use Excel's MROUND Function How to Nest Multiple IF Functions in Excel How to Use the DAY function in Excel How to Use the COUNTIF Function in Excel How to Combine Two Columns in Excel Ignore Error Values When Finding the Average in Excel How to Use the Round Function in Excel Using Formulas for Conditional Formatting in Excel How to Count Data in Selected Cells With Excel's COUNTIF Function How to Use a Dynamic Range in Excel With COUNTIF and INDIRECT Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies