Microsoft Office Tutorials and References
In Depth Information
Using functions in your formulas
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
calculations that are not otherwise possible. Say 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. 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 function
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
Copy to copy the range. Next, with B1:B1000 still
selected, choose Home Clipboard Paste arrow 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.
Clipboard
One last example should convince you of the power of functions. Suppose you have a
worksheet that calculates sales commissions. If the salesperson sold more than $100,000
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 that 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. 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 percent. This example uses
three arguments, separated by commas. I discuss this in the upcoming section, “Function
arguments.”
Search JabSto ::




Custom Search