How to use/create formulas in R1C1 mode

Original Title: "MODE R1C1"
Hi, I would like to know how to use/ create formulas in R1C1 mode. Thank you
 

Question Info


Last updated November 3, 2019 Views 2,560 Applies to:
Answer
Answer
go to Excel Menu > Preferences > General set the first item  (which is use R1C1)

This is the way Lotus 1-2-3 did things (Lotus 1-2-3 no longer exist for the Mac) and and In MS olden Days when Excel first came out and they wanted to Convert as many lotus users away as possible they provided this setting.

Basically the way Excel does things and Lotus does things are much the same.  the difference being  that for Rows Lotus use R which stands for Row and Excel uses a Number; then Lotus uses C for Column and Excel uses the Alphabet.

The differences between the two in Lotus R1C1 method the row marker is pointed to first then the Column Marker second to pick up the desired cell. Excel does the opposite it points to the column used then the row used to pick up the cell desired.

Other than the row Column Making system Excel and Lotus did things much the same way when they first came out.

I used Lotus 1-2-3 when I was using an Apple II. and When I switched a Mac I had to go with Microsoft Works, Then  went  to Word/Excel.

I kinda Like the RC method better, but switched to the Excel method when I bought Works then Excel.

Lotus 1-2-3 billed itself as Database and Spreadsheet program. While Excel has always billed itself as a Spreadsheet program only (in advertising). even though It actually does both . For example: Tasks like working with numbers and doing some type of Math is Spreadsheet work. On the other hand creating list of names and address or an inventory list is obviously a Database.
If my reply has helped, mark accordingly - Helpful or Answer
Phillip M. Jones, C.E.T.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer
Answer
If your question was actually focused on the "use/create" in  "how to use/create formulas in R1C1 mode" rather than how to turn on R1C1 mode:


R1C1 style, as the name implies, uses R as a prefix for Row number, C as a prefix for Column number. Absolute addressing is indicated by the number being plain, relative addressing is indicated by the number being enclosed in brackets. For instance:

R1C1 A1-style Description
=============================================
R1 $1:$1 The current Row (absolute)
R 1:1 The current Row (relative)
C1 $A:$A The current Column (absolute)
C A:A The current Column (absolute)
R1C1 $A$1 Absolute reference to first row, first column
R[2]C $A3 Two rows down in same column
R[-2]C $Ax Two rows up in the same column
R2C[4] y$2 Four columns to the right in row2
R[4]C[-2] yx Four rows down, two columns to the left


So the equivalent of this in cell D7:

    =B7 + H5 - INDEX($1:$1,3)

is this in cell R7C4:

   =RC[-2] + R[-2]C[4] - INDEX(R1,3)

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.