Specifications

www.pcmag.com SEPTEMBER 16, 2003 PC MAGAZINE
61
an array function (a function that performs
multiple calculations on multiple values):
=SUM(IF(NOT(ISERROR(A2:A6)),A2:A6,""))
8
The LARGE function will return the
nth largest number in a list. If you
have a list of test scores in cells A2:A10,
you can find the third-best score with this
function:
=LARGE(A2:A10,3). There is a
similar function,
SMALL, which finds the
nth smallest number in the list.
9
SUBTOTAL calculates a subtotal for a
list, which may be useful when you
are using a filtered list. The problem
with using
SUM with a filter is that the
function totals both hidden and visible
values.
SUBTOTAL, however, sums only
the visible values. Instead of writing the
SUBTOTAL function yourself, click on the
AUTOSUM button on the toolbar and it will
write the correct
SUBTOTAL function.
10
To calculate the square root of a
number, Excel uses the
SQRT func-
tion; for example,
=SQRT(25) calculates
the square root of 25. When you need, say,
a cube root, you must work with the
mathematical idea that the cube root is
calculated by raising the number to the
power of
1
/
3
. So calculate the cube root of
27 using
=27^(1/3). This principle ex-
tends to let you find the root of any num-
ber by raising it to a fractional power.
11
Look-up functions find data in tables.
If you have a list of office names in
column A and sales figures in
columns B and C, the function
=VLOOKUP("Seattle",A2:C15,2,
FALSE)
looks for Seattle in column A
of the data table (A2:C15) and re-
turns the corresponding value from
column B (the second column in the
table). Use
FALSE in the formula to
tell Excel that the data is not sorted
and that an exact match is required.
12
The function =TODAY() places
the current date in a cell. You
can use this with a macro to save a
file using today’s date as its file-
name. This sample macro code saves the
file using the contents of cell A1 as the
filename. Simply place
=Today() in cell A1
and run the following macro to test it:
Sub savenamefromcell()
Dim savename AsString
savename=Sheets(1).Range("A1").Value
& ".xls"
ActiveWorkbook.SaveAs Filname:
=savename
End Sub
13
Use the FREQUENCY function to count
the instances a particular number
occurs in a series of values. The function
requires a set of ranges (or bins) to group
the values. For example, use bins of 5, 10,
15, and 20 to report the frequency of val-
ues in the ranges 0:5, 6:10, 11:15, and 16:20.
Because
FREQUENCY is an array function,
you must first select a range of cells the
same size as the bin range, then type the
function
=FREQUENCY (A1:D15, F2:F5)
then press Ctrl-Shift-Enter. This example
uses bins in the range F2:F5 to count num-
bers in the range A1:D15.
14
Creating dynamic ranges is easy
with the
OFFSET function. For exam-
ple, this function, used in the Insert | Name
dialog, names a list of numbers in column
A, assuming the list begins in cell A1 and
there are no blank cells in the range:
=OFFSET($A$1,0,0,COUNTA($A:$A),1). If
you name your range, for example, Filled-
Cells, the function
=SUM(FilledCells) will
sum the values in the list. The results up-
date automatically as numbers are added
or removed. The arguments for the
OFFSET
function are the starting or reference cell;
the number of rows and columns up,
down, or across from the reference cell;
and the number of rows and columns to
return. The
OFFSET function is quite useful
for creating charts that update as new data
is added. For a working example, see
“Automatic Charting” (www.pcmag.com
/article2/0,4149,33331,00.asp).
15
The future value function, FV, calcu-
lates the return on a given invest-
ment. To calculate the ten-year value of
$1,000 invested today at 5 percent interest
(compounded monthly),
=FV(5%/12,
10*12,,-1000)
returns $1,647.01. If you
make additional monthly payments of
$10 per month, the future value is
=FV(5%/12,10*12,-10,-1000) and returns
$3,199.83. Negative values are used because
you are paying out money, and you should
take care to scale the interest rate to match
the periods used. We’re assuming 12 peri-
ods per year, so the interest rate is
5%/12.
Little-known functions like these open
new avenues of productivity and fun for
Excel users.
Helen Bradley is a contributing editor of
PC Magazine.
MAKING TECHNOLOGY WORK FOR YOU
SOLUTIONS
62 Hardware: Universal
Plug and Play.
64 Office: Customize
fonts on your own.
68 Security Watch:
Anonymous e-mail.
70 Internet Business:
Amazon everywhere..
73 User to User:
Tips and tricks.
Show m
with h
TIP 15: The FV function helps you figure
out the long-term value of an investment.
TIP 14: OFFSET lets you create charts that update
dynamically when values are added to the source data.