excel2.pdf

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 re...

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. 1 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 2 A mixed cell reference contains a fixed column and relative row or a fixed row and a relative column. 3 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. 4 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. 5 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. 6 7 8 9 10 11 12 13 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. 14 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. 15 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. 16 It is possible to use one function as an argument to another, in a nested fashion. Examples of nested functions are: =IF(A1