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.
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.