IF function

This article describes the formula syntax and usage of the IFfunction in Microsoft Office Excel.

Description

The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10.

Syntax

IF(logical_test, value_if_true, [value_if_false])

The IF function syntax has the following arguments:

  • logical_test    Required. Any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.

  • value_if_true    Required. The value that you want to be returned if the logical_test argument evaluates to TRUE. For example, if the value of this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, the IF function returns the text "Within budget." If logical_test evaluates to TRUE and the value_if_true argument is omitted (that is, there is only a comma following the logical_test argument), the IF function returns 0 (zero). To display the word TRUE, use the logical value TRUE for the value_if_true argument.

  • value_if_false    Optional. The value that you want to be returned if the logical_test argument evaluates to FALSE. For example, if the value of this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, the IF function returns the text "Over budget." If logical_test evaluates to FALSE and the value_if_false argument is omitted, (that is, there is no comma following the value_if_true argument), the IF function returns the logical value FALSE. If logical_test evaluates to FALSE and the value of the value_if_false argument is blank (that is, there is only a comma following the value_if_true argument), the IF function returns the value 0 (zero).

Remarks

  • Up to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. (See Example 3 for a sample of nested IF functions.) Alternatively, to test many conditions, consider using the LOOKUP, VLOOKUP, HLOOKUP, or CHOOSE functions. (See Example 4 for a sample of the LOOKUP function.)

  • If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out.

  • Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF or the COUNTIFS worksheet functions. To calculate a sum based on a string of text or a number within a range, use the SUMIF or the SUMIFS worksheet functions.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.Important Do not select the row or column headers.

    selecting an example from help

    Selecting an example from Help

  2. Press CTRL+C.

  3. Create a blank workbook or worksheet.

  4. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.

  5. Important For the example to work properly, you must paste it into cell A1 of the worksheet.

  6. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

  7. After you copy the example to a blank worksheet, you can adapt it to suit your needs.

1

2

3




4




5

A

B

C

Data

50

23

Formula

Description

Result

=IF(A2<=100,"Within budget","Over budget")

If the number in cell A2 is less than or equal to 100, the formula returns "Within budget." Otherwise, the function displays "Over budget."

Within budget

=IF(A2=100,A2+B2,"")

If the number in cell A2 is equal to 100, A2 + B2 is calculated and returned. Otherwise, empty text ("") is returned.

Empty text ("")

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.Important Do not select the row or column headers.

    selecting an example from help

    Selecting an example from Help

  2. Press CTRL+C.

  3. Create a blank workbook or worksheet.

  4. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.

  5. Important For the example to work properly, you must paste it into cell A1 of the worksheet.

  6. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

  7. After you copy the example to a blank worksheet, you can adapt it to suit your needs.

1

2

3

4

5


6


7

A

B

C

Actual Expenses

Predicted Expenses

1500

900

500

900

500

925

Formula

Description

Result

=IF(A2>B2,"Over Budget","OK")

Checks whether the expenses in row 2 are over budget

Over Budget

=IF(A3>B3,"Over Budget","OK")

Checks whether the expenses in row 3 are over budget

OK

Example 3

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.Important Do not select the row or column headers.

    selecting an example from help

    Selecting an example from Help

  2. Press CTRL+C.

  3. Create a blank workbook or worksheet.

  4. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.

  5. Important For the example to work properly, you must paste it into cell A1 of the worksheet.

  6. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

  7. After you copy the example to a blank worksheet, you can adapt it to suit your needs.

1

2

3

4

5


6


7


8

A

B

C

Score

45

90

78

Formula

Description

Result

=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F"))))

Assigns a letter grade to the score in cell A2

F

=IF(A3>89,"A",IF(A3>79,"B", IF(A3>69,"C",IF(A3>59,"D","F"))))

Assigns a letter grade to the score in cell A3

A

=IF(A4>89,"A",IF(A4>79,"B", IF(A4>69,"C",IF(A4>59,"D","F"))))

Assigns a letter grade to the score in cell A4

C

The preceding example demonstrates how you can nest IF statements. In each formula, the fourth IF statement is also the value_if_false argument to the third IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement, and the second IF statement is the value_if_false argument to the first IF statement. For example, if the first logical_test argument (Average>89) evaluates to TRUE, "A" is returned. If the first logical_test argument evaluates to FALSE, the second IF statement is evaluated, and so on. You can also use other functions as arguments.

The letter grades are assigned to numbers, using the following key.

If Score is

Then return

Greater than 89

A

From 80 to 89

B

From 70 to 79

C

From 60 to 69

D

Less than 60

F

Example 4

In this example, the LOOKUP function is used instead of the IF function because there are thirteen conditions to test. You may find the LOOKUP function easier to read and maintain than the IF function.

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.Important Do not select the row or column headers.

    selecting an example from help

    Selecting an example from Help

  2. Press CTRL+C.

  3. Create a blank workbook or worksheet.

  4. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.

  5. Important For the example to work properly, you must paste it into cell A1 of the worksheet.

  6. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

  7. After you copy the example to a blank worksheet, you can adapt it to suit your needs.

1

2

3

4

5




6




7




8

A

B

C

Score

45

90

78

Formula

Description

Result

=LOOKUP(A2,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})

Assigns a letter grade to the score in cell A2

F

=LOOKUP(A3,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})

Assigns a letter grade to the score in cell A3

A-

=LOOKUP(A4,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})

Assigns a letter grade to the score in cell A4

C+

Applies To: Excel 2007



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language