Imagine a function as a complete computing program that can perform certain calculations. Each function has a unique name, which usually starts with a short description of the arithmetic problem solved by the function. Functions perform complex mathematical, statistical, technical, or logical calculations; search for certain information; and manipulate text.
Functions are mostly used in formulas, although you can use them in names and macros. (Names are covered in Chapter 5, and macros are covered in Chapter 6.) Functions can be nested or used multiple times within formulas.
In Microsoft Office Excel 2003, a formula can include up to seven levels of nested functions. In Excel 2007 and Excel 2010, up to 64 levels are supported. The user—not the function—must ensure that the arithmetic correctness of the formula is maintained.
Functions not only make it easier to create formulas, they are also faster. You should always try to use a function instead of writing your own formulas. Functions can:
Quickly perform calculations
Use less space within a formula
Reduce the risk of typing errors
To perform a calculation, functions require information (data), which is referred to as arguments or parameters in Excel. In Excel, the Help facility for functions provides comprehensive information on the number and type of required arguments, as well as guidance on the optional parameters.
The syntax of a function is the input rule and sets out the exact format required for the function. If you don’t follow the syntax exactly, you get an error message from Excel.
The syntax of a function always follows these rules (see Figure 4-3):
A function always starts with the function name. If the function is at the start of a formula, enter an equal sign first.
The name is followed by a opening parenthesis. The opening parenthesis tells Excel where the arguments start. Spaces are illegal before and after a parenthesis.
Arguments are entered after the opening parenthesis. The arguments have to match the data type required by the function. Some functions have optional arguments, which are not required to run the function. You will find more information in Excel Help.
Arguments can be constants or formulas matching or calculating the required data type:
Arguments are separated by commas (,).
If an argument requires the Text data type, the text has to be in quotation marks. If an argument is a calculation, this calculation can also contain functions.
If you enter a function for an argument of a function, this function is said to be nested. In Excel 2003, a formula can include up to seven levels of nested functions. This was extended to 64 in Excel 2007.
After you have entered all of the arguments, insert a closing parenthesis to complete the function.
Remember always to use parentheses in pairs. This means that for each opening parenthesis you have to enter a closing parenthesis.
The data passed to a function for processing is referred to as arguments. In this book, in Excel Help, or in other reference books, the description of a function outlines the details of the required arguments, the optional arguments, and the data type:
You can use any expression (a constant, a formula, and so on) as an argument as long as the expression (or its result) matches the required data type.
In the Function Wizard (explained in more detail in the section titled Using the Function Wizard later in this chapter), the required arguments are bold. Optional arguments appear in normal type.
Multiple arguments are separated by commas (,).
Avoid spaces within functions, because they can cause errors. This is a general rule when writing formulas.
To pass arguments, you have to consider the required data type. Table 4-1 shows the data types used in Excel.
Table 4-1. Data Types Used in Excel
Data Type | Sample Function | Possible Input |
|---|---|---|
Number | =SUM(number1,number2,...) | A number (constant); a cell reference; a formula with a number as its result |
Text | =CONCATENATE(Text1,Text2,...) | Text in quotation marks; a cell reference |
Cell reference | =ROW(reference) | A cell reference |
Date/Time Value | =MONTH(number) | A number; a date in quotation marks; a cell reference |
Logical | =AND(logical value1,logical value2,...) | Comparison output; TRUE; FALSE; a cell reference |
You should also make sure that the data is passed efficiently. The faster the data is passed, the faster the function works. This is demonstrated in the following example.
Consider the SUM() function in Figure 4-4. What options are available for passing arguments to this function?
You can pass the four values as constants, as shown in this example:
=SUM(27.5,20.0,12.5,30.0)
Even though the function calculates the correct result, it misses the point of a spreadsheet. If you change a value in the table, you also have to modify the function accordingly.
You can pass each value as a cell reference, as in this example:
=SUM(B2,B3,B4,B5)
This function also returns the correct result. The input is correct but inconvenient, because the syntax could be shorter.
You can pass the four values as an addition formula, as in:
=SUM(B2+B3+B4+B5)
The function calculates the correct result again, because the sum of the four cells is a numeric value. However, this doesn’t make any sense. If the addition is already performed in the parentheses, why do you still need the SUM() function? If constantly repeated, such meaningless arguments impact the calculation speed. (However, this example does show that an arithmetic expression can be passed as an argument.)
If a function requires several numeric values that result from a series of calculations, it is possible to include the calculations directly in the formula itself.
This arithmetic problem is explained in the example in Figure 4-5. The initial row calculations could be performed independently, and then the SUM() function used to combine the results. It is, however, possible to include the calculations directly in the SUM() function.
In this case, the formula looks like this:
=SUM(B2+D2,B3-D3,B4*D4,B5/D5)
For the SUM() function, the four arguments to be calculated can be entered in any order. In sum calculations, the order of the arguments doesn’t matter. Remember: A function is always solved from the inside out.
But of course, the summation with the =SUM(E2:E5) formula would be faster and clearer if partial results were available in column E.