Excel enables a skilled user to make business, financial, and scientific computations quickly and conveniently.
Excel provides built-in worksheet functions that can be applied in formulas to facilitate these computations. Last month we looked at the basic functions. This month we’ll introduce two tools that will make your work with functions and formulas much easier.
Formulas in Excel
Formulas are usually thought of as mathematical equations but in a spreadsheet they are much more. A formula in a spreadsheet can perform many different kinds of operations on data in the worksheet (see Figure 1). In this example, the formula tests the contents of two worksheet cells and performs a specific calculation if the cells both have a number in them.
Figure 1: The formula tests the contents of two worksheet cells
Excel formulas do computation, logical comparison, and concatenation (putting strings of text together) using data in worksheet cells. The cells may be on the same worksheet, on other worksheets, or even in other workbooks.
When you create a formula in Excel, you begin by selecting a cell of the worksheet where it will be stored. After you select the cell, you enter the formula in the formula bar, beginning with an equal sign (=) to let Excel know this is a formula. Later, if a user selects that cell, Excel displays the formula in the formula bar again, and the formula result in the cell itself.
Formulas can be very simple expressions, or they can be quite complex. Here are some simple formulas as they would appear in the formula bar:
=(2+1) causes 3 to be displayed in the cell.
=(A1+B1+C1+D1) causes the sum of the numbers in cells A1, B1, C1, and D1 to be displayed.
=IF(ISNUMBER(A1),”Number”,”Try Again”) causes the word “Number” to be displayed in the cell if there is a number in cell A1, or “Try Again” if there is not.
=(C7*B9)+((1-C7)*B9) subtracts the number in C7 from 1, multiplies it by the number in B9, and adds the result to the product of the numbers in cells C7 and B9.
You can enter simple expressions like these directly into the formula bar, with little chance for error.
Function Junction
However, business, financial, and scientific computations can be more involved than these examples. For about 200 of the most common operations, Excel provides worksheet functions. Some of these worksheet functions are simply convenient, such as SUM, which adds the values stored in the cells you specify and displays the total. In one of the examples above, we could replace =(A1+B1+C1+D1) by =SUM(A1:D1) and get the same result.
Other worksheet functions perform complicated tasks. For example, PRICEMAT calculates the price per $100 face value of a security that pays interest at maturity. (PRICEMAT is a special function that may not have been installed on your system.)
You could enter the complete formula into the formula bar yourself to calculate the price (see Figure 2). Or you could use PRICEMAT (see Figure 3). Which one do you think is faster to enter accurately?
Figure 2: Entering the complete formula into the formula bar to calculate the price
Figure 3: Using PRICEMAT to calculate the price
Excel groups its functions into nine groups or categories:
- Financial
- Date and Time
- Math and Trig
- Statistical
- Lookup and Reference
- Database
- Text
- Logical
- Information
Functions consist of two parts — a function name and one or more arguments. An argument is data the function requires in order to return a result and display it. PRICEMAT, as you saw in the previous figure, requires six arguments: the settlement date, maturity date, issue date, interest rate, annual yield, and the type of day count basis to use in the calculation. SUM can find a total of up to 30 arguments, each of which can be the numeric contents of a cell, a logical value (0 for FALSE, 1 for TRUE), or the numeric result from another formula or function (see Figure 4).
Figure 4: SUM can find a total of up to 30 arguments
This is great, but there is a downside. The challenges to using functions in formulas are:
- Remembering more than 200 functions and what each one does;
- Remembering the proper syntax for a function (syntax is the way in which the function must be written in order for Excel to interpret it correctly); and
- Typing in a big formula without getting a detail wrong.
Many formulas require nested functions, for example. What’s a nested function? It’s a function used as an argument inside another function (see Figure 5). Fortunately, Excel offers two tools to help you build such formulas and others that are far more complex: Paste Function and the Formula Palette. Rather than typing formulas and functions from memory directly into the function bar, you can use these tools to speed your work.
Figure 5: A nested function
The Paste Function organizes and defines functions so you don’t have to remember or look up all the details such as the arguments. It also offers a list of the functions you used most recently (see Figure 6).
Figure 6: The Paste Function offers a list of the functions you used most recently
The Formula Palette also provides help for the memory, especially where the arguments are concerned, but it is mainly a workbench for long formulas (see Figure 7). The Formula Palette evaluates each expression as it is entered, so you can ensure your formula will deliver the required results. This can be critical with nested functions, where even a misplaced parenthesis can have a huge impact.
Figure 7: The Formula Palette is mainly a workbench for long formulas
You would probably not use these two features for every formula. There is little advantage to using the Formula Palette when the formula uses no functions, unless it is so complex and contains so many nested elements that it is difficult to enter correctly.
The Paste Function and the Formula Palette are partners. Most of the time when you use the Formula Palette, you will also use the Paste Function. For its part, the Paste Function automatically opens the Formula Palette after you have chosen a function.
Notwithstanding this partnership, there are two ways to start entering a formula. You may begin with the Paste Function, especially if you are only using one formula in a cell. If you are building a formula, it may be best to start with the Formula Palette.
Using the Paste Function
To use the Paste Function, you may go to the menu bar and choose Insert, Function. You may press and hold the Shift button while pressing F3. Or, you may click on the Paste Function icon in the button bar (see Figure 8).
Figure 8: The Paste Function button
In any case, the Paste Function dialog box will open. The dialog box automatically inserts the equal sign (=) that indicates a formula (see Figure 9).
Figure 9: The Paste Function dialog box inserts the equal sign (=) that indicates a formula
Select a function from the Function name column. The Paste Function enters the function in the formula bar complete with a pair of parentheses ready to receive the arguments.
The Paste Function then opens the Formula Palette with entry boxes for the arguments (see Figure 10). In the case of this simple example, these steps are all that is required in order to calculate the average operating miles for the three vehicles. Notice that the Formula Palette shows what is in the selected cells and computes the formula result.
Figure 10: The Paste Function opens the Formula Palette with entry boxes for the arguments
Using the Formula Palette
To enter a more complicated formula, begin with the Formula Palette. To do this, click on the Edit Formula button to the left of the formula bar (see Figure 11).
Figure 11: The Edit Formula button
Excel will automatically enter the equal sign (=) in the formula bar and will change the name box (to the left of the Edit Formula button) to a drop-down function list. If you have recently used a function, its name will appear in this drop-down list (see Figure 12). In this case, we just used the AVERAGE function, so it appears on the button itself.
Figure 12: The name of a recently used function appears in the drop-down list
In the next example we will be calculating the Fleet Average Operating Cost per mile, so we can begin by clicking this button (see Figure 13). The Formula Palette opens; the Palette can be dragged out of the way, incidentally, as it is here to give you a better view.
Figure 13: Calculating the fleet average operating cost per mile
In this case, Excel tries to insert a range as the first argument, but this is not what you want. You want to total up the number of miles Vehicle 1 has traveled in the first quarter. You have probably used the SUM function recently, so click on the drop-down box to open it and choose SUM from the list (see Figure 14). If the SUM function had not appeared on the list, you would have clicked on the More Functions… choice. At that point, the Paste Function dialog box would appear and you could make the choice from there.
Figure 14: Choosing SUM from the drop-down list
Excel inserts the SUM function into the formula. Next, select the cells to be totaled. You can do this either by clicking or dragging within the cells, or you can type the cell names directly. Either way, Excel minimizes the Formula Palette to improve visibility (see Figure 15).
Figure 15: Selecting the cells to be totaled
Repeating this process as needed, you can construct the entire formula (see Figure 16). As you proceed, if the formula contains nested functions, the argument boxes on the Formula Palette change to show which function is currently selected. The name of the selected function will also be shown in bold in the formula bar, as a further aid. If you need to stop entering a formula, click the Cancel button on the Palette. When your entry is complete, click OK and the Formula Palette will close.
Figure 16: Constructing the entire formula
The Formula Palette won't allow you to make mistakes in the arguments or the syntax. If you make a spelling mistake, Excel will highlight the mistake by printing #NAME? in the Palette.
These features can save you many hours in creating custom formulas. They are worth taking the time to build a few “tryout” worksheets, so that you will be comfortable with the process involved.
