Excel IF Formula Examples: Tests, Logic, and Errors

Excel’s IF formula checks whether a condition is true or false and returns one result for each outcome. With a score in B2, =IF(B2>=60,"Pass","Fail") returns Pass when the score is 74.

What the IF formula does

IF is a logical function: it evaluates a test, then displays a result based on whether that test is true. It is useful for translating a rule into a worksheet result—for example, marking invoices as paid, identifying low stock, or assigning a status to a score.

The function does not change the value in the cell it checks. Instead, it displays its chosen result in the cell containing the formula. If the input changes, Excel recalculates the result automatically. IF can return text, a number, a date, or the result of another calculation.

IF syntax and the three arguments

The general syntax is =IF(logical_test,value_if_true,value_if_false). The logical test is a comparison or expression that evaluates to TRUE or FALSE. The second argument is returned when the test is true; the third is returned when it is false. Both result arguments are required in this syntax.

Common comparison operators include equal to (=), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=). To compare text, put the text in straight double quotation marks. Cell references and numbers do not need quotation marks. Excel text comparisons in ordinary IF formulas are not case-sensitive, so a test for “paid” also matches “Paid”.

In Excel for Microsoft 365 on Windows, the function can be entered directly in a cell or through the Insert Function control. The examples here use commas between arguments. Depending on Windows regional settings, Excel may display or require semicolons as separators; use the separator your installation expects.

Basic pass-or-fail and status examples

A threshold check is the simplest common use. With exam scores in B2:B30 and a passing mark of 60, enter this formula in C2 and fill it down. If B2 contains 74, the result is Pass; if it contains 58, the result is Fail.

With the score to test in B2, use =IF(B2>=60,"Pass","Fail"). The formula returns Pass for 74 and Fail for 58.

IF can also compare a cell with text. Suppose column C contains invoice status and the formula goes in D2. If C2 contains Paid, the result is Complete; if it contains Pending, the result is Follow up. Matching text must be enclosed in quotation marks.

With the status in C2, use =IF(C2="Paid","Complete","Follow up"). For C2 equal to Paid, the result is Complete; for Pending, it is Follow up.

To return a number rather than a label, omit quotation marks around the numeric result. For instance, a commission rule can apply one rate below a sales threshold and another rate at or above it. This formula assumes the sales amount is in B2: at 12,000 it calculates 600, while at 8,000 it calculates 160.

With sales in B2, use =IF(B2>=10000,B2*5%,B2*2%). At 12,000, the result is 600; at 8,000, the result is 160.

Testing more than one condition with AND and OR

Use AND inside IF when every requirement must be met. For example, eligibility might require both a score of at least 70 and a completed status. AND returns TRUE only when all its tests are true, so failing either requirement sends the formula to the false result.

With the score in B2 and completion status in C2, use =IF(AND(B2>=70,C2="Complete"),"Eligible","Not eligible"). For a score of 78 and status Complete, the result is Eligible; if the status is Incomplete, it is Not eligible.

Use OR when meeting any one of several conditions is enough. In this example, an order qualifies for review if it is marked Urgent or its value is at least 5,000. OR returns TRUE when at least one test is true.

With the order type in A2 and value in B2, use =IF(OR(A2="Urgent",B2>=5000),"Review","Standard"). An order marked Urgent with a value of 300 returns Review; a Standard order worth 2,000 returns Standard.

AND and OR can accept more than two logical tests. Keep the rule readable, though: when a formula has many conditions, arrange the rules in a table or break the logic into helper columns so it is easier to inspect and maintain.

Nested IF and IFS for several outcomes

A nested IF places one IF inside another to check several possible ranges. Excel evaluates the tests from left to right and uses the first true result. The order matters: put the highest threshold first so a high score is not caught by a lower threshold earlier in the formula.

With a score in B2, this formula assigns a letter grade. For a score of 86 it returns B; 72 returns C; and 55 returns F. The final F acts as the result when none of the earlier thresholds is met.

With the score in B2, use =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F")))). A score of 86 returns B, 72 returns C, and 55 returns F.

For a long chain of separate tests, IFS can be easier to read because it pairs each condition with its result. IFS is available in Excel 2019 and later, including Microsoft 365. It returns the result for the first true condition, so order still matters. In versions that do not support IFS, use nested IF formulas instead.

With the score in B2, use =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F"). A score of 86 returns B, and the final TRUE condition supplies F when every grade threshold fails.

Handling blank cells and dates

A blank check can help prevent a worksheet from showing a status before anyone has entered data. A test comparing a cell to an empty string also treats a formula that returns an empty string as blank. If it matters whether a cell is truly empty rather than merely displaying nothing, use the ISBLANK function instead.

With an entry expected in A2, use =IF(A2="","Missing","Entered"). If A2 is genuinely empty, the result is Missing; if A2 contains text such as North, the result is Entered.

Dates are stored as serial values in Excel, so they can be compared with date functions or cell references. Use DATE to build an unambiguous date in a formula rather than typing a date string whose interpretation might vary with regional settings. In this example, a deadline of December 31, 2026, is inclusive.

With the submitted date in B2, use =IF(B2<=DATE(2026,12,31),"Within deadline","Late"). A date of December 15, 2026, returns Within deadline; a date of January 5, 2027, returns Late.

Using IF with calculations and errors

IF can select between calculations, not just labels. That makes it useful for applying a cap, choosing a rate, or preventing an unwanted calculation when an input is missing. Check that each branch returns a value appropriate for its purpose; a formula that produces a plausible-looking number can still reflect a poorly defined rule.

For example, to cap a reimbursable amount at 250, return the entered amount when it is 250 or less and return 250 otherwise. This assumes the amount is in B2: an amount of 180 returns 180, while an amount of 310 returns 250.

With the amount in B2, use =IF(B2<=250,B2,250). The results for 180 and 310 are 180 and 250, respectively.

IFERROR is related but solves a different problem: it returns a fallback value if an expression produces an error. It does not test a business condition like IF. Use it thoughtfully, because replacing every error with zero can hide missing or invalid inputs. Here, dividing 18 by zero produces an error, so the formula displays a prompt instead.

With the numerator in A2 and denominator in B2, use =IFERROR(A2/B2,"Check inputs"). If A2 is 18 and B2 is 0, the result is Check inputs; if B2 is 3, the result is 6.

Common IF formula problems and how to fix them

Missing quotation marks: Text results such as Pass need straight double quotes. Without them, Excel may interpret the word as a name rather than text.
Reversed outcomes: The second argument is for TRUE and the third is for FALSE. Check the order if the formula returns the opposite label from the one expected.
Wrong comparison operator: A threshold using greater than is different from one using greater than or equal to. Decide whether the boundary value itself should qualify.
Numbers stored as text: Imported values may look numeric but behave like text. Check the source data and, where appropriate, convert the values to numbers before relying on comparisons.
Extra spaces in text: A cell containing Paid followed by a space may not match the expected text. Clean the source data or use TRIM in an appropriate helper column.
Unbalanced parentheses: Nested IF formulas need a closing parenthesis for every opening one. Excel’s formula bar and color highlighting can help identify the nesting.
Unexpected blank results: A formula may be returning an empty string by design, or the referenced cell may be empty. Check the formula’s false result and the source cell.

When a result is surprising, test the logical condition on its own in a temporary cell, then check the values and data types in the referenced cells. This separates a faulty condition from an issue with the returned text or calculation.

When to use IF—and when not to

Use IF when a result depends on a yes-or-no test. AND and OR extend that decision to multiple requirements, while nested IF or IFS handles several possible outcomes. If the task is simply to count or sum cells matching a condition, COUNTIF or SUMIF may be more direct than building a column of IF results and then summarizing it.

Do not confuse a formula result with conditional formatting. IF writes a value into a cell; conditional formatting changes how a cell looks when a rule is met. For example, a status formula can display Late, while conditional formatting can color that status red. The two features can be used together, but they serve different purposes.

For matching a key to a value in a table, a lookup function may be easier to maintain than a long chain of IF statements. Microsoft 365 and Excel 2021 include XLOOKUP, which can search a range and return a corresponding result. Keep IF for explicit decision rules and use a lookup when the logic is really a mapping table.

Frequently Asked Questions

Can an IF formula return a blank? Yes. With a value in A2, =IF(A2="","","Ready") returns an empty-looking result when A2 is blank and Ready otherwise. The cell still contains a formula, even though the formula displays no text in the first case.

Can IF check text values? Yes. Compare the cell with the text in quotation marks, such as testing whether a status equals Paid. Ordinary IF text comparisons do not distinguish between uppercase and lowercase, though extra spaces can cause a match to fail.

How many nested IF functions can I use? Excel allows up to 64 nested levels of functions. That is a technical limit, not a recommendation: long nested formulas are difficult to audit, so consider IFS, a lookup table, or helper columns when the rules grow.

Why does Excel show a formula error after I paste an example? Check the argument separator required by your regional settings, the quotation marks, and the parentheses. Some regional configurations use semicolons instead of commas, even though the examples here show standard comma-separated syntax.

Can IF return a number and text? Yes. The true and false results can be different types, such as a number in one case and a text label in the other. However, mixing types may complicate later calculations, so choose outputs that suit how the result will be used.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.