CHAPTER 6 Flashcards
Entering formulas and functions
In Excel, you can use formulas and functions to work with data. We start a formula or a function with an equal to ‘=’ sign.
Formula to calculate sum
SUM(number1, [number2],…). Excel indicates that the SUM function requires at least one argument, with a second argument [shown in square brackets] as optional.
Relative Referencing
Excel defaults to a relative referencing system. That is when we copy a formula from one cell to the other, Excel changes the cells to which formula refers, relative to the cell containing the formula.
Absolute Referencing
On occasion, we may not want the formula to be adjusted automatically. We may want to freeze the row and/or column of the starting cell in the range and/or ending cell in the range. To freeze the respective row or column, we prefix the appropriate row or column reference with a dollar sign. Using the
dollar sign in front of a row or column reference makes that reference an absolute one.
Toggle between the dollar signs for the row and column
A quick way to toggle between the dollar signs for the row and column is to position the cursor on the cell reference and press F4. We can press F4 again to toggle between relative row and relative column,
absolute row and relative column, absolute row and absolute column, relative row and absolute column references.
R1C1 Format for Cell Reference
The default reference for a cell in Excel is the A1 format. When we use A1 referencing, the letter – which comes first – refers to the column, and the number refers to the row. An alternative system of cell referencing is the R1C1 format. The R1C1 format refers to a cell using
numbers for both the row reference and the column reference. In the R1C1 format, the row comes first.
R1C1 Referencing
In the cell reference “RC[-2]” the letter R simply indicates the current row. On the other hand, the letter C for column is followed by [-2]. The -2 in square brackets indicates that the column referred to is two columns to the left. A positive number, e.g., [2] would indicate a column reference two columns to the right.
In R1C1 referencing, a row or column followed by a number that is not in square brackets indicates an absolute reference. For example, R5C10 is the equivalent of $J$5.
Active Cell Reference
a) A7: R7C1
b) A8: R8C1
c) F12: R12C6
d) C23: R23C3
e) J22: R22C10
f) XET1048569: R1048569C16374
a) Active Cell: A7, A1 Reference = D7 + F9, R1C1
Reference: RC[3] + R[2]C[5]
b) Active Cell: A8, A1 Reference = H6 + K4, R1C1
Reference: R[-2]C[7] + R[-4]C[10]
c) Active Cell: F12, A1 Reference = H6 + H16, R1C1
Reference: R[-6]C[2] + R[4]C[2]
d) Active Cell: C23, A1 Reference = $B$26 + $A$21,
R1C1 Reference: R26C2 + R21C1
e) Active Cell: J22, A1 Reference = K20 + $H$25,
R1C1 Reference: R[-2]C[1] + R25C8
f) Active Cell: XET1048569, A1 Reference =
XEV1048567 + XEQ1048572, R1C1 Reference:
R[-2]C[2] + R[3]C[-3]
Why R1C1 NOTATION?
The R1C1 was used by earlier spreadsheet programs, such as Lotus 1-2-3. When Microsoft introduced Excel, Microsoft offered the R1C1 notation in addition to the A1 notation to make it easier for users to switch from Lotus 1-2-3 to Excel.
A second reason we use R1C1 referencing is that it provides consistency in entering formulas, making it easier for us to find errors in our formula notation.
The A1 reference changes from cell to cell, the R1C1 is identical. This visual consistency is helpful in confirming the accuracy of our formulas.
The R1C1 reference is always in relation to the active cell. The A1 reference does not depend on the active cell. For instance =A3B3 typed in any cell always refers to A3B3; however, the meaning of “=RC[-
4]*RC[-3]” will change depending on which cell we enter this formula.
A third reason for using the R1C1 notation is that understanding this type of referencing will facilitate our working with macros.
In the R1C1 format, cell address XEV1048563 is:
R1048563C16376
You are in cell J10. If you put = R[-5]C[-2]-3 + R[6]C[2], this is equivalent to:
= H5-3 + L16
What does the absolute reference do?
The row and/or column reference will not change if the
formula containing the reference is copied to another
location.
If there is a formula” =Sum (B3:B8)” in cell B9, what is the formula when you copy it into B12?
=Sum (B6:B11)
In a formula, how do we freeze a row or column reference?
We prefix the appropriate row or column reference with a dollar sign.
