Page 223 - Excel 2007 Bible

16_044039 ch11.qxp 11/21/06 11:04 AM Page 180
Part II
Working with Formulas and Functions
New Functions in Excel 2007
E
xcel 2007 contains five new functions:
n IFERROR — Used to check for an error, and display a message or perform a different calculation.
n AVERAGEIF @md Used to calculate a conditional average (similar to SUMIF and COUNTIF).
n AVERAGEIFS — Used to calculate a conditional average using multiple criteria.
n SUMIFS — Used to calculate a conditional sum using multiple criteria.
n COUNTIFS — Used to calculate a conditional COUNT using multiple criteria.
In addition, worksheet functions that formerly required the Analysis ToolPak add-in (which is shipped with
Excel) are now built into Excel. So you have access to dozens of additional functions without installing the
add-in.
These new functions are described in detail in the Excel Help, and I present examples in later chapters.
Keep in mind that if you use any of these new functions, you will not be able to share your workbook with
someone who uses an earlier version of Excel.
One last example should convince you of the power of functions. Suppose you have a worksheet that calcu-
lates 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 used 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.
Function arguments
In the preceding examples, you may have noticed that all the functions used parentheses. The information
inside the parentheses is called the list of arguments.
Functions vary in how they use arguments. Depending on what it has to do, a function may use
n No arguments
n One argument
n A fixed number of arguments
n An indeterminate number of arguments
n 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 provide a set of empty parentheses, like this:
=NOW()

180