Formulas and Functions
Creating and Editing Formulas
Several enhancements have been added to Excel that make it easier to create and edit formulas, especially if you use worksheet functions. To use a function in Excel 95, you either type in the function or select the Function Wizard button on the Standard toolbar.
In Excel 97 and 2000, Paste Function 1 replaces the Function Wizard. The Paste Function command expands on the features that were part of the Function Wizard, making it easier to use the built-in worksheet functions.
Figure 3.21 shows the Paste Function dialog box 2; the Office Assistant 3 appears asking whether you need help using the dialog box. If you don't know which function to use, select Yes in the Office Assistant pop-up box and type a description of what you want to do. The Office Assistant will suggest which worksheet functions for you to use.
After you select the function to use, choose OK. A dialog box for the function you selected appears (see Figure 3.22). This dialog box is referred to as the Formula Palette. The Formula Palette dialog box is anchored below the Formula Bar 4. This dialog box 5 lists each function argument and displays a description of both the function 6 and the selected argument 7.
Often, Excel selects the range of cells to use in the function. In this example, Excel has selected cells B4:B15 8. This would create an inaccurate average, because the Year Total in cell B15 is included in the range. A feature in this dialog box enables you to 9 collapse the dialog box to just one row to more easily select cells in your worksheet. After you have selected the cells, you can then expand the dialog box using the same button.
TIP
You cannot select range names from the Name Box on the Formula Bar while the Formula Palette is displayed. To include a range name in a formula, press F3.
FIG. 3.21 Paste Function dialog box in Excel 2000.
FIG. 3.22 Formula Palette dialog box in Excel 2000.
New Functions
Several new functions were added to Excel in version 97. Many of the new functions include options for working with text or logical values (True/False). Text and the False logical value are interpreted as zero (0). The True logical value is interpreted as one (1). For example, the AVERAGEA function lets you average a list that contains numbers, text, and logical values. Figure 3.23 compares the AVERAGE function to the AVERAGEA function. The formula 1 for the AVERAGEA function appears in the Formula Bar. The result 2 of the formula appears in cell F9.
-
AVERAGEASimilar to AVERAGE. Returns the average of the values in a range of cells. In addition to numbers, cells that contain text and logical values, such as TRUE and FALSE, are included in the calculation; AVERAGE does not include text and logical values in calculations.
-
GETPIVOTDATARetrieves data from a PivotTable.
-
HYPERLINKCreates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet.
-
MAXASimilar to MAX. Returns the largest value in a range of cells. In addition to numbers, cells that contain text and logical values, such as TRUE and FALSE, are included in the calculation; MAX does not include text and logical values in calculations.
-
MINASimilar to MIN. Returns the minimum value in a range of cells. In addition to numbers, cells that contain text and logical values, such as TRUE and FALSE, are included in the calculation; MIN does not include text and logical values in calculations.
-
STDEVASimilar to STDEV. Estimates standard deviation based on a sample of a population. In addition to numbers, cells that contain text and logical values, such as TRUE and FALSE, are included in the calculation; STDEV does not include text and logical values in calculations.
-
STDEVPASimilar to STDEVP. Calculates standard deviation based on an entire population. In addition to numbers, cells that contain text and logical values, such as TRUE and FALSE, are included in the calculation; STDEVP does not include text and logical values in calculations.
-
VARASimilar to VAR. Estimates the variance of a sample of a population. In addition to numbers, cells that contain text and logical values, such as TRUE and FALSE, are included in the calculation; VAR does not include text and logical values in calculations.
-
VARPASimilar to VARP. Calculates the variance based on the entire population. In addition to numbers, cells that contain text and logical values, such as TRUE and FALSE, are included in the calculation; VARP does not include text and logical values in calculations.
FIG. 3.23 An example of the new function AVERAGEA.