Page 238 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

Row and Column cell references (R1C1)

Excel normally displays cell references in the A1 style i.e with rows each given a number and columns given an
alphabetic character.
Absolute or Relative cell references determine whether ranges are incremented as they are copied to other
cells. Placing the dollar symbol ($) in front of a column letter or row number will fix it if the formula is copied to
other cells. If the relative formula SUM(A5:A10) is copied to the adjacent cell, it will update to SUM(B5:B10). The
absolute formula SUM($A$5:$A$10) will refer to the same range, regardless of where it is copied to.

Excel can also handle cell references using a different format or notation. The R1C1 notation describes a cell
location in terms of a row number and a column number. It can also distinguish between absolute and relative
references. R5C1 is an absolute reference (for cell $A$5) whereas R[4]C[1] is four rows below and one column
to the right, relative to the current cell.

In the above example the formulas in Cells C9, D9 and E9 are shown in both A1 and R1C1 notation. The first
formula contains absolute references. The next two are relative formulas. Note however that in R1C1 format,
these two are identical.

Select File | Options and Formulas to modify the worksheet appearance and display references as R1C1.
Formulas change and column labels will switch from letters to numbers.

Use Excel options to toggle the Style Style Worksheet with R1C1 references ON

These alternative R1C1 references can be applied using the Indirect function without switching Excel's formula
properties. e.g. =SUM(INDIRECT("R[-5]C[0]:R[-1]C[0]",0)) will sum the five cells above the current cell. Note that
an extra argument must be supplied to the Indirect function. =INDIRECT(TextRng, Type_TF). If TextRng is an
R1C1 reference, the optional Type_TF argument must be set to FALSE or zero. If it is any other value or omitted
the TextRng is assumed to be in normal A1 notation.
The next example uses the Indirect function to read the R1C1 style reference in cell F1. That string in turn reflects
the month number in cell D2. The reference currently represents the first 3 columns of values on the current row.

225

CITS : IT&ITES - Computer Software Application - Lesson 63 - 77