Example of How to Use Formulas with the IF Function in Excel

Excel offers many functions for performing conditional analyses. The term conditional analysis means executing different operations depending on certain conditions.

The condition is a variable value and a logical one: TRUE or FALSE. Depending on these values, the formula can execute two different calculations. If the condition returns TRUE, the first expression is calculated, and the second is skipped. If the condition returns FALSE, the first expression is skipped, and the second is executed.



Logical Analysis with a Condition Formula in the IF Function Argument

The image below shows a table with cities and fuel prices over a 6-month period. Let's say we need to determine whether the price each month was above or below the average price for all cities that month. If the price is above average, it should be labeled "High"; if below, "Low." The results of the conditional analysis are found in a separate table below.

=IF(C3>AVERAGE(C$3:C$12),"High","Low")
table with cities and fuel prices.

The IF function is fundamental for performing conditional analyses in Excel. It contains 3 arguments:

IF function arguments.
  1. Condition.
  2. The formula expression executed if the condition in the first argument returns TRUE.
  3. The second expression is executed if the condition returns FALSE.

In this example, the first argument with the condition contains the formula: C3>AVERAGE(C$3:C$12). The condition must be constructed so that it returns a logical value of TRUE or FALSE. Hence, a comparison operator is typically used (e.g., greater >, less <, equal =, not equal <>, greater than or equal to >=, etc.). Alternatively, a logical function can be used (e.g., =ISBLANK(), =OR(), =AND(), etc.). Any formula or function can be used if its result is TRUE or FALSE. In this example, the first argument of the IF function contains a condition with the greater than operator (>), which compares the value in cell C3 with the average of the cell range C$3:C$12.

In our formula, we use a relative reference to cell C3, which changes when copying the formula to other cells. A mixed absolute reference is also used for the cell range C$3:C$12, which changes only relative to columns but not rows when copying the formula.

If the condition in the first argument returns TRUE, the current cell is filled with the text from the second argument of the IF function. In this case, the second argument contains the word "High." Since the value in cell C3 is greater than the average of the range C$3:C$12, the word "High" is entered in cell C14.

In cell C17, the result compares cell C6 to the average price for August:

IF and AVERAGE.

Download an example of how to use formulas with the IF function in Excel download file

Since the price is lower, the condition returns FALSE, and the formula outputs the text from the third argument, "Low." And so on.