The effect is only cosmetic; the results will not change. As you shall see

later, what you have just done will facilitate the understanding of

functions.

In addition, leave the option VIEW/ FORMULA BAR selected as shown in

Figure 2.

Figure 2: Select “Formula Bar”

26

Chapter 1: Writing Formulas

1.2.A THE “A1” VS. THE “R1C1“ STYLE OF CELL REFERENCES

The next figure shows a simple formula. The formula is written into cell

G15. The formula multiplies the values inside cells F8 and F6.

Figure 3: A!-style cell referencing

This style of referencing is called the “A1“ style or “absolute” referencing.

The exact location of the referenced cells is written. (The cells are those

in the 6th and 8th rows of column F.) One typically works with this style.

However, there is another style for referencing the cells in a formula.

This style is called the “R1C1“ style or “relative” referencing. The same

formula as in the previous figure but in R1C1 style is shown in the next

figure.

Figure 4: The same formula as in the previous figure, but in R1C1 (Offset) style cell

referencing while the previous figure showed A1 (Absolute-) style cell referencing

Does not this formula look different? This style uses relative referencing.

So, the first cell (F8) is referenced relative to its position in reference to

the cell that contains the formula (cell G15). Row 8 is 7 rows below row

15 and column F is 1 column before column G. Therefore, the cell

reference is “minus seven rows, minus 1 column” or “R[” 7]C[” 1].”

If you see a file or worksheet with such relative referencing, you can

switch all the formulas back to absolute “A1” style referencing by going to

TOOLS/OPTIONS/GENERAL and deselecting the option “R1C1 reference

style.”

27

Statistical Analysis with Excel

Figure 5: Settings for Formula Referencing

1.2.B WRITING A SIMPLE FORMULA THAT REFERENCES CELLS

Open the sample file “File3.xls” and choose the worksheet “main.”

Assume you want to write add the values in cells C2231 and D223 (that is,

to calculate “C223 + D223”) and place the result into cell F223.

Click on cell F223. Key-in “=“and then write the formula by clicking on

the cell C223, typing in “+” then clicking on cell “D223.”

Figure 6: Writing a formula

After writing in the formula, press the key ENTER. The cell F223 will

contain the result for the formula contained in it.

Figure 7: The result is shown in the cell on which you wrote the formula

Cell C223 is the cell in column C and row 223.

1

28

Chapter 1: Writing Formulas

TYPES OF REFERENCES ALLOWED IN A FORMULA

1.3

1.3.A REFERENCING CELLS FROM ANOTHER WORKSHEET

You can reference cells from another worksheet. Choose cell H235 on the

worksheet “main.” In the chosen cell, type the text shown in the next

figure. (Do not press the ENTER key; the formula is incomplete and you

will get an error message if you press ENTER.)

Figure 8: Writing or choosing the reference to the first referenced range

Then select the worksheet “second” and click on cell D235. Now press the

ENTER key. The formula in cell H235 of worksheet “main” references the

cell D235 from the worksheet “second”. The next figure illustrates this.

Figure 9: Writing or choosing the reference to the second referenced range which is not on the

worksheet on which you are writing the formula

In this formula, the part “second!” informs Excel that the range referenced

is from the sheet “second.