How to Use the IF-THEN Function in Excel (2024)

This article explains how to write and use the IF function (also known as IF-THEN) in Excel. Instructions apply to Microsoft 365, Excel 2021, 2019, 2016, 2013, 2010; Excel for Mac, and Excel Online.

How to Write IF-THEN in Excel

The IF functionin Excel is a way to add decision-making to yourspreadsheets. It testsa condition to see if it's true or false and then carries out a specific set of instructions based on the results.

For example, you could set an IF to turn back results 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."

The IF-THEN function's syntax includes the name of the function and the function arguments inside the parenthesis.

This is the proper syntax of the IF-THEN function:

 =IF(logictest,valueiftrue,valueiffalse)

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 sets of instructions with a comma between them.

• The first item tells the function what to do if the comparison is true.
• The second item tells the function what to do if the comparison is false.

A Simple IF-THEN Function Example

Before moving on tomore complex calculations, let's look at a straightforward example of an IF-THEN statement.

Our spreadsheet is set up with cell A1 as $100. We can input the following formula into B1 to indicate whether the value is larger than $1000.

 =IF(A1>1000,"PERFECT","TOOSMALL")

This function has the following parts:

• A1>1000 tells Excel to check whether the value in cell A1 is larger than 1000.
• "PERFECT" returns the word PERFECT in cell B1 if A1 is larger than 1000.
• "TOO SMALL" returns the phrase TOO SMALL in cell B1 if A1 is not larger than 1000.

In plain language, this IF function says, "If the value in A1 is greater than 1,000, write PERFECT. Otherwise, write TOO SMALL."

The comparison part of the function can compare only two values. Either of those two values can be:

• Fixed number
• A string of characters (text value)
• Date or time
• Functions that return any of the values above
• A 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. You can make the IF function very advanced by embedding additional calculations or functions inside it.

Inputting Calculations Into the IF-THEN Function

You can embed different calculations for the IF function to perform, depending on the comparison results. This example uses one calculation for the tax rate, 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)

If the value in B2 is greater than 50,000, the IF function will multiply it by 0.15. If it's lower, the function will multiply it by 0.10.

You can also embed calculations into the comparison side of the function. In the above example, you might estimate that taxable income will only be 80% of total income. With this in mind, you can change the above IF function to the following:

 =IF(B2*0.8>50000,B2*0.15,B2*0.10)

This formula first multiplies the input value (in this case, B2) by 0.8, and then it compares that result to 50,000. The rest of the function works the same.

Nesting Functions Inside of an IF Function

You can also embed (or "nest") a function inside an IF statement in Excel. This action 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 in Excel:

 =IF(AVERAGE(B2:B6)>85,"Excellent!","NeedsWork")

In English: "If the average of the values from B2 to B6 is greater than 85, type Excellent! Otherwise, type 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.