Use conditional formulas in Excel to analyze financial data

In Microsoft Office Excel 2003, you can use the IF function to create conditional formulas that analyze data and return a value based on the results of the analysis. For example, you can configure your worksheet to:

  • Display a message when a condition is true, for example, "Overdue" when an unpaid invoice is over 30 days old.
  • Return a value based on the results of a calculation, such as a discount percentage if an invoice is paid within 30 days of the invoice date.
  • Cross-check for errors. For example, display an error message if row and column totals don't agree.
  • Prevent the #DIV/0! error value from appearing when the divisor field is blank or 0 (zero).

The IF function

The IF function uses the following arguments.

IF Function

Callout 1  logical_test: the condition you want to check

Callout 2  value_if_true: the value to return if the condition is true

Button image  value_if_false: the value to return if the condition is false

Display a message if a condition is true

You can display a message based on a value or the results of a calculation. For example, you may want to display "Overdue" for unpaid invoice items more than 30 days old.

  1. In cell D7, type =IF((TODAY()-B8)>30, "Overdue", "Current"), and then press ENTER.
  2. Select cell D7, and then drag the fill handle Fill Handle  over the range of cells that you want to display the message.

If today's date is more than 30 days after the invoice date, the value of the cell is "Overdue." Otherwise, the value is "Current."

Condition-based message

Callout 1  Enter the formula in this cell.

Callout 2  Drag the fill handle over this range of cells.

Enter a value based on a condition

Excel can calculate a value, such as an invoice discount, based on the results of another cell or calculation. In this example, a 3 percent discount is calculated and entered if the invoice is paid within 30 days.

  1. In cell E7, type =IF((C7-B7)<31,D7*0.03,0), and then press ENTER.
  2. Select cell E7, and then drag the fill handle Fill Handle over the range of cells that you want to contain this formula.

If the Date Received is less than 31 days after the Invoice Date, the value of the discount is the Invoice Amount multiplied by 3 percent. Otherwise, the value is 0 (zero).

Condition-based value

Callout 1  Enter the formula in this cell.

Callout 2  Drag the fill handle over this range of cells.

Display an error message

Error messages can help you monitor the accuracy of your worksheet formulas. For example, you can create an error message that appears if row and column totals don't agree.

  1. In the cell that you want to contain the error message (cell E6 in this example), type =IF(SUM(D2:D5)=D6, "","Error"), and then press ENTER.

If the value of the sum of cells D2 to D5 is equal to the value of cell D6, the formula returns nothing. Otherwise, the formula returns "Error."

  1. To make the error message more visible, you can change the formatting. Select the error message cell, and then click a button on the Formatting toolbar.

Formatting toolbar

Condition-based error message

Prevent the #DIV/0! error from appearing

The #DIV/0! error appears when the divisor field in a precedent field is blank or 0 (zero). In this example, the formula =D5/C5 creates an error when cell C5 is blank.

To keep the error from appearing, use the IF worksheet function.

  1. In cell E5, type =IF(C5=0,"",D5/C5), and then press ENTER.

The two quotation marks represent an empty text string.

  1. Select cell E5, and then drag the fill handle Fill handle over the range of cells that you want to contain this formula.

#DIV/0! error

 
 
Applies to:
Excel 2003