Excel Formulas and Functions PDF

This chapter reviews how to perform quantitative analysis, focusing on formulas and functions. 1 Excel offers three types of cell references for use when a formula is copied. An absolute cell reference (for examp...

This chapter reviews how to perform quantitative analysis, focusing on formulas and functions. 1 Excel offers three types of cell references for use when a formula is copied. An absolute cell reference (for example, $A$1) indicates that the address will be fixed and will not be altered during a copy operation. A relative reference (for example, A1) indicates that the address will be adjusted relative to its new location. A mixed reference (for example, $A1 or A$1) has one part that is absolute and the other relative. The F4 key can be used to toggle through the four types of cell references. 2 In Figure 2.2, the textbook illustrates a formula with an absolute cell reference. The down payment rate in cell B4 is the same for each row and should not be altered during a copy operation. For example, when the formula =D3*$B$4 is copied from row 3 to row 4, the formula become =D4*$B$4 3 A mixed cell reference contains a fixed column and relative row or a fixed row and a relative column. 4 A circular reference would occur if the formula written in cell E2 is =E2*-$C$4. Since the formula is self-referential and cannot be evaluated, the error message in Figure 2.5 will appear. Excel displays a green triangle in the top-left corner of a cell if it detects a potential error in a formula. 5 An Excel function is a predefined formula available in many categories. Function categories and their descriptions are listed in Table 2.1. Some categories include Date & Time, Financial, Logical, and Statistical. 6 The function =SUM(A1:A3) exhibits correct syntax and is well formed. The function begins with an equal sign (=), then lists the function name (SUM). The argument or input to this function is a range of cells (A1:A3). This function returns the mathematical sum of values in the range A1 to A3. 7 8 9 10 11 12 13 14 15 Figure 2.1 in the textbook illustrates formulas using relative cell references. For example, when the formula =D2-E2 is copied from row 2 to row 3, the formula become =D3-E3. 16 The SUM function returns the mathematical sum of some number of cells or ranges. Multiple cells or ranges are separated by a comma. Examples include: =SUM(A1:A3), which returns the sum of values in the range A1 to A3 =SUM(A1,B3,C5), which returns the sum of values in the three cells A1, B3, and C5 =SUM(A1:B3,C5:E8), which returns the sum of values in the range A1 to B3 and C5 to E8 Do not use a formula as an argument in this case. For example, =SUM(A1+A2) computes the addition of A1 and A2, and then returns the sum of that one value. It is a misuse of the SUM function. 17 Common statistical functions include the following: AVERAGE returns the average or arithmetic mean of a range. MEDIAN returns the midpoint value, halfway between the lowest and highest value in a range. MIN returns the smallest or minimum value in a range. MAX returns the largest or maximum value in a range. COUNT returns the number of numeric values in a range. COUNTBLANK returns the number of empty cells in a range. COUNTA returns the number of nonempty cells in a range. 18 See Figure 2.14 for a display of the basic statistical functions. 19 It is possible to use one function as an argument to another, in a nested fashion. Examples of nested functions are: =IF(A1