Chapter 9: Introducing Formulas and Functions
9
One last example should convince you of the power of functions. Suppose you have a
worksheet that calculates sales commissions. If the salesperson sold $100,000 or more of
product, the commission rate is 7.5 percent; otherwise, the commission rate is 5.0 percent.
Without using a function, you would have to create two different formulas and make sure
you use the correct formula for each sales amount. A better solution is to write a formula
that uses the IF function to ensure that you calculate the correct commission, regardless
of sales amount:
=IF(A1<100000,A1*5%,A1*7.5%)This formula performs some simple decision-making. The formula checks the value of cell
A1, which contains the sales amount. If this value is less than 100,000, the formula returns
cell A1 multiplied by 5 percent. Otherwise, it returns what’s in cell A1 multiplied by 7.5 per-
cent. This example uses three arguments, separated by commas. We discuss this in the next
section, “Function arguments.”
Function arguments
In the preceding examples, you may have noticed that all of the functions used parenthe-
ses. The information inside the parentheses is the list of arguments.
Functions vary in the way they use arguments. Depending on what it has to do, a function
may use one of the following:
■ No arguments■ (^) One argument
■ A fixed number of arguments
■ (^) An indeterminate number of arguments
■ Optional arguments
An example of a function that doesn’t use an argument is the NOW function, which returns
the current date and time. Even if a function doesn’t use an argument, you must still pro-
vide a set of empty parentheses, like this:
=NOW()
If a function uses more than one argument, separate each argument with a comma. The
examples at the beginning of the chapter used cell references for arguments. Excel is quite
flexible when it comes to function arguments, however. An argument can consist of a cell
reference, literal values, literal text strings, expressions, and even other functions. Here
are some examples of functions that use various types of arguments:
Cell reference: =SUM(A1:A24)
Literal value: =SQRT(121)
Literal text string: =PROPER(“john f. smith”)
Expression: =SQRT(183+12)
Other functions: =SQRT(SUM(A1:A24))