Exploring the R1C1 Cell Reference Style

Problem

You've seen the use of the A1 style of cell reference in Recipe 1.5 and are wondering if there are other ways to refer to cells.

Solution

Yes, there are other ways to refer cells, such as the R1C1 style.

Discussion

Besides using the A1 style of cell reference, you can also use the so-called R1C1 style of cell reference. This is not the default style, but in some cases it can be more intuitive or conducive to matrix operations and programming, as we'll see later.

The R1C1 style uses numbers to identify both rows and columns in a spreadsheet. For example, R1C1 refers to the cell in row 1 column 1. To use the R1C1 style you must first activate it. To do so, go to the Tools Options... menu to open the Options dialog box. Once its open, press the General tab (see Figure 1-12).

Figure 1-12. General tab in the Options dialog box

Check the box next to R1C1 reference style under the Settings heading to activate the R1C1 style. When you press OK and return to your spreadsheet, you'll see that the column headings have changed from letters to numbers, as shown in Figure 1-13.

Figure 1-13. R1C1 reference style

Also notice that Excel automatically changed the formulas. For example, the formula in cell D7 (now R7C4) was =B7*C7; now it's =RC[-2]*RC[-1], which is a relative reference in R1C1 style.

When using the R1C1 style, if you enter a cell reference like R3C5 (i.e., R followed by a number and C followed by another number), you are using absolute cell references. The equivalent in A1 style would be $E$3. Using brackets around the number following either R or C indicates relative cell references. For example, R[1]C[2] refers to the cell one row down and two rows to the right of the cell containing that reference. The cell two rows up and one row to the left would be referred to as R[-2]C[-1]. An R or C not followed by a number or a number in brackets refers to the same row or column as the cell containing the reference.

See Also

To learn more about cell references, see Recipe 1.7 and Recipe 1.14. You can also check out the Excel Help topic "About cell and range references."

Using Excel

Getting Acquainted with Visual Basic for Applications

Collecting and Cleaning Up Data

Charting

Statistical Analysis

Time Series Analysis

Mathematical Functions

Curve Fitting and Regression

Solving Equations

Numerical Integration and Differentiation

Solving Ordinary Differential Equations

Solving Partial Differential Equations

Performing Optimization Analyses in Excel

Introduction to Financial Calculations

Index



Excel Scientific and Engineering Cookbook
Excel Scientific and Engineering Cookbook (Cookbooks (OReilly))
ISBN: 0596008791
EAN: 2147483647
Year: N/A
Pages: 206
Authors: David M Bourg

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net