Part II: Working with Formulas and Functions
Part II: Working with Formulas and Functions
even impossible) if you use only the operators discussed previously. For example, you can
use the TAN function to calculate the tangent of an angle. You can’t do this complicated
calculation by using the mathematical operators alone.
Examples of formulas that use functions
A worksheet function can simplify a formula significantly.
Here’s an example. To calculate the average of the values in ten cells (A1:A10) without
using a function, you’d have to construct a formula like this:
=(A1+A2+A3+A4+A5+A6+A7+A8+A9+A10)/10
Not very pretty, is it? Even worse, you would need to edit this formula if you added another
cell to the range. Fortunately, you can replace this formula with a much simpler one that
uses one of Excel’s built-in worksheet functions, AVERAGE:
=AVERAGE(A1:A10)
The following formula demonstrates how using a function can enable you to perform calcu-
lations that are not otherwise possible. Say that you need to determine the largest value in
a range. A formula can’t tell you the answer without using a function. Here’s a formula that
uses the MAX function to return the largest value in the range A1:D100:
=MAX(A1:D100)
Functions also can sometimes eliminate manual editing. Assume that you have a worksheet
that contains 1,000 names in cells A1:A1000 and the names appear in all-capital letters.
Your boss sees the listing and informs you that the names will be mail-merged with a form
letter. Use of all-uppercase letters is not acceptable; for example, JOHN F. SMITH must
now appear as John F. Smith. You could spend the next several hours re-entering the
list (ugh), or you could use a formula, such as the following, which uses the PROPER func-
tion to convert the text in cell A1 to the proper case:
=PROPER(A1)
Enter this formula once in cell B1 and then copy it down to the next 999 rows. Then select
B1:B1000 and choose Home ➪ Clipboard ➪ Copy to copy the range. Next, with B1:B1000 still
selected, choose Home ➪ Clipboard ➪ Paste Values (V) to convert the formulas to values.
Delete the original column, and you’ve just accomplished several hours of work in less than
a minute.
You can also use Excel’s Flash Fill feature to make this type of transformation, without formulas. See Chapter 25,
“Importing and Cleaning Data,” for more about Flash Fill.
