Advertisements

IF Function

Applies to: Excel for Office 365, Excel for Office 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

The IF function is one of Excel’s most recognised functions, enabling you to make logical comparisons between a value and your expected outcome.

Therefore, an IF statement can yield two results. The first result is if your comparison is True, the second if your comparison is False.

For example, =IF(A2=”Yes”,1,2) says IF(A2 = Yes, then return a 1, or else return a 2).

Syntax

Employ the IF function, one of the logical functions, to obtain one value if a condition is true and an alternative value if it’s false.

IF(logical_test, value_if_true, [value_if_false])

For example:

  • =IF(C2>A2,”Over Budget”,”OK”)
  • =IF(C2=A2,C4-A4,””)
Argument name Description
logical_test (required) The condition you decide to test.
value_if_true (required) The value that you prefer to be returned if the result of logical_test is TRUE.
value_if_false (optional) The value that you expect to be returned if the result of logical_test is FALSE.

Note: If you want to use text in formulas, you must wrap the text in quotes (e.g. “Text”). However, the single exception to the rule is using TRUE or FALSE, which Excel intuitively understands.

Common problems

ProblemWhat went wrong
0 (zero) in cell An absence of an argument for either value_if_true or value_if_False arguments. To see the correct value returned, apply the argument text to the two arguments, or insert TRUE or FALSE to the argument.
#NAME? in cell This typically indicates that the formula is misspelled.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Calculation operators and precedence in Excel

Use nested functions in a formula

Using IF to check if a cell is blank

Video: Advanced IF functions

IFS function (Office 365, Excel 2016 and later)

Advanced IF functions – Working with nested formulas and avoiding pitfalls

Training videos: Advanced IF functions

The COUNTIF function will count values based on a single criteria

The COUNTIFS function will count values based on multiple criteria

The SUMIF function will sum values based on a single criteria

The SUMIFS function will sum values based on multiple criteria

AND function

OR function

VLOOKUP function

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

Logical functions

Excel functions (alphabetical)

Excel functions (by category)

Advertisements

Leave a Reply

%d bloggers like this: