Microsoft Office Tutorials and References

In Depth Information

**Mastering Excel’s Built-in Functions**

Use the IFERROR function to avoid the possibility that a formula will return an error. Its

syntax is IFERROR(value,value_if_error). If you’re performing division operations and some cells

in the column used for the divisor equal 0, you risk seeing #DIV/0! errors in those cells. To

eliminate that possibility, use a formula like this one: =IFERROR(B2/C2,0), which returns 0 in

place of any error.

To reverse the logic of any argument, use the NOT function.

Statistical Functions

For students of statistics, the functions in this group are invaluable. If you need to calculate

confidence intervals, Poisson distributions, standard deviations, or even the “one-tailed

probability of the chi-squared distribution,” you’ve come to the right place. For those of us

who aren’t math majors, a few functions in this group are still handy for everyday use.

To calculate the arithmetic mean of a set of numbers, use the AVERAGE function. Although

you can enter arguments individually, you’re most likely to specify part of a row or column

as the range to use for this calculation. The AVERAGEIF and AVERAGEIFS functions return

the arithmetic mean of all cells that meet criteria you define.

The arithmetic mean is what most nonmathematicians think of when they hear the term

average.
For a slightly different calculation, use the MEDIAN function, which finds the

number that is in the middle of a set of numbers.

To find the lowest or highest value in a set or a range, use the MIN or MAX function,

respectively. Use the COUNT function to calculate how many numbers are in the referenced

range or list of arguments. Four additional functions that begin with COUNT allow you to

calculate the number of values (COUNTA), blank cells (COUNTBLANK), or the number of

cells that meet a single criterion (COUNTIF) or multiple criteria (COUNTIFS).

Math and Trigonometry Functions

We promised we wouldn’t bring up unpleasant memories of math homework, and we’ll be

true to our word here. If you’re a math student or an honest-to-goodness rocket scientist,

you’ll want to study the entire list of functions in this category. You’ll find the usual suspects

from trigonometry and math here: sine and cosine and tangent (SIN, COS, TAN), arcsine

and arc cosine and arctangent (ASIN, ACOS, and ATAN), and even hyperbolic tangent

(TANH) and inverse hyperbolic cosine (ACOSH).

If you’re a civilian (mathematically speaking), you’ll benefit most from a group of

mathoriented functions that all involve rounding numbers.