Microsoft Office Tutorials and References

In Depth Information

When you work with an
IF
function, the Function Arguments dialog box has three boxes:

Logical_test, Value_if_true, and Value_if_false. The Logical_test box holds the condition

you want to check. If the customer’s year-to-date shipping bill appears in cell G8, the

expression would be
G8>100000
.

Now you need to have Excel display messages that indicate whether Craig Dewar should

evaluate the account for a possible rate adjustment. To have Excel print a message from

an
IF
function, you enclose the message in quotes in the Value_if_true or Value_if_false

box. In this case, you would type
“High-volume shipper—evaluate for rate decrease.”
in

the Value_if_true box and
“Does not qualify at this time.”
in the Value_if_false box.

Excel also includes several other conditional functions you can use to summarize your data,

shown in the following table.

Function
Description

AVERAGEIF
Finds the average of values within a cell range that meet a given criterion

AVERAGEIFS
Finds the average of values within a cell range that meet multiple criteria

COUNT
Counts the number of cells in a range that contain a numerical value

COUNTA
Counts the number of cells in a range that are not empty

COUNTBLANK
Counts the number of cells in a range that are empty

COUNTIF

Counts the number of cells in a range that meet a given criterion

Counts the number of cells in a range that meet multiple criteria

COUNTIFS

Displays one value if a formula results in an error and another if it doesn’t

IFERROR

Finds the sum of values in a range that meet a single criterion

SUMIF

Finds the sum of values in a range that meet multiple criteria

SUMIFS