Specifications

4
The DATEDIF function, undocument-
ed in most Excel versions, returns the
time between two dates, measured in
your choice of completed years, complet-
ed months, or days. This function is
handy for calculating a person’s age. Put
the person’s birth date in cell A1 and
write this formula in cell B1 to calculate
the age in years:
=DATEDIF(A1,NOW(),"y")
5
The NETWORKDAYS function calculates
the number of workdays (excluding
weekends) between two dates. You can
specify holidays that should be excluded
from the count. Place your holiday dates
in column A, then select them and click
on Insert | Name | Define and name the
range holidays. Place the start and
end dates in cells B1 and C1 and use
this function to calculate the num-
ber of workdays between the two:
=NETWORKDAYS(B1,C1,holidays)
6
Use the CONVERT function to con-
vert measurements from one
unit to another. For example, to
convert a value in cell A2 from inch-
es to centimeters, use this function:
=CONVERT(A2,"in","cm"). Excel Help
contains a complete list of the conversions
and function arguments to use for each.
7
The ISERROR function returns True
when the cell to which it refers con-
tains an error and
False when it doesn’t.
Combine this with
NOT and an IF function
to create a string that adds a range of num-
bers, ignoring any cells that contain errors.
So, if your numbers appear in the range
A2:A6, type this function and press Ctrl-
Shift-Enter to enter it into the cell, as it is
the check box titled Analysis
ToolPak, and click on
OK. If
Analysis ToolPak is not installed,
you’ll be prompted to install it.
Second, the purpose of this arti-
cle is to whet your appetite for
these functions; we don’t have the
space to cover their uses in detail.
To find more information about
any of these functions, type the
function name in Excel Help.
1
The COUNTIF function counts the num-
ber of times a condition is met. For ex-
ample, if you have a list of days in a
month in column A (cells A1:A31) and
your sales receipts for those days in col-
umn B, you can count the number of days
that your sales exceeded $5,000 with this
function:
=COUNTIF(B1:B31,">5000"). A
similar function,
SUMIF, totals values
instead of counting them.
2
The CHOOSE function takes a number
from 1 to 29 and a list of items (up to
29) and returns the item that corresponds
to the number. One use for this function is
to return the day of the week for a given
date. To do this, couple it with the
WEEKDAY
function, which gives a day number (1 to 7)
for a date, then use
CHOOSE to turn the
number into a day name. Assuming your
date is in cell B2, use this function to get
the day of the week it falls on:
=CHOOSE(WEEKDAY(B2), "Sun", "Mon",
"Tue", "Wed", "Thu", "Fri", "Sat")
3
MOD (or modulus) returns the remain-
der when one number is divided by
another. For example,
=MOD(4,3) returns
1. Use this function to format every other
row of a worksheet by select-
ing the cells to format and
choosing Format | Condition-
al Formatting. Choose Formu-
la Is and type this formula:
=MOD(ROW(),2). (ROW returns
the current row number.)
Click on Format and set a pat-
tern for alternate rows on the
Patterns tab. Click on
OK
when you’re done.
15 Great Excel Tips
www.pcmag.com/solutions
Use these little-known functions to make your formulas more
useful than ever.
BY HELEN BRADLEY
Microsoft Excel is jam-packed with functions that perform a range of handy
calculations and tests. We take a look at 15 Excel functions you may not know
about and show you some clever ways to put them to work on your data.
Two words of warning: First, some of these functions work only when the
Analysis ToolPak add-in is enabled. To do this, choose Tools | Add-ins, select
TIP 4: DATEDIF is helpful when you want to calculate
someone’s age.
TIP 5: NETWORKDAYS finds the number of workdays in a
given range, excluding weekends and holidays.
MORE ON THE WEB
You’ll find another five Excel tips, plus helpful
pointers on an array of computing tasks, at
www.pcmag.com.