CCNC/CCNC Module 4/Working with Formulas and Functions/Cell referencing

From WikiEducator
Jump to: navigation, search

Cell referencing

(Comment.gif: A large segment of this sub-section was copied from Free Computer Tutorials (see reference) from the section entitled Absolute versus relative cell referencing.)

At times you may wish to copy the data in one cell to another, that is, you want to have the data in one cell appear in another. This procedure is known as cell referencing. Let's see how it works starting with the screen below

SCREENSHOT HERE As you can see, it's quite simple. All we are doing is adding together the numbers in cells A1 and A2. We then put the answer in cell B2. But suppose we wanted to copy the formula in cell B2 to cell B3. Let's try it and see what happens.

  • Click inside cell B2
  • Then click once with your right mouse button

A menu pops up

  • With your left mouse button, click Copy
  • Now click inside cell B3
  • Click once with your right mouse button
  • When the menu pops up, click on Paste

Your spreadsheet will look like the one below:

SCREENSHOT HERE

Relative cell referencing

Calc has done something rather odd. It has given us the answer 25! The sum 20 + 25 clearly does not equal 25, so what's going on? Well, look inside the formula bar. The formula is now reading = A2 + A3. Yet that was not the formula we pasted from cell B2. We copied and pasted the formula = A1 + A2. So why did Calc copy and paste the wrong formula? The answer is that we used a Relative cell reference for B2. This is Calc’s default, and it works like this when you copy a formula:

The formula is = A1 + A2. The answer to that formula is in cell B2. When copying the formula to cell B3 Calc will note that the cells for the formula start UP one Row, and LEFT one column. When you paste the formula somewhere else, Calc does not paste the original formula, but rather it pastes a formula that corresponds to the cells that are UP one then Left one. So starting at Cell B3, which is where we're pasting to, go UP one Row. This takes you to row 2. Then go one column Left. This takes you to Column A. So the start for the new formula is cell A2. Your formula will now read:

= A2 + A3. As there is nothing in cell A3, the formula is really = 25 + 0. Which gives the answer 25. And that's Relative cell referencing.

Absolute cell referencing

If you want to keep a reference to the original cells, A1 and A2, you need to use Absolute cell references. Absolute cell referencing is done with dollar signs. Change your formula in cell B2 to this: = $A$1 + $A$2 Then copy and paste the new formula to cell B3. You should now get the answer you were looking for: 45. Your spreadsheet will look like the one below:

SCREENSHOT HERE

To recap, then:

  • When you want to copy and paste formulas, use Absolute cell references
  • When you put the $ symbol in front of the column letter and the row number, then the reference will not change.

Cell referencing for a range of cells

The following two screens illustrate relative and absolute cell referencing respectively when the formula from a single cell is copied to a range of cells.

Relative cell referencing for a range of cells

Suppose cell D4 contains the formula =D1-D2. Suppose we now copy the contents of D4 to E4:G6.

The result is shown on the following screen:

SCREENSHOT HERE


If you look at this screen, you will see that both the column letters and row numbers change when the cell D4 is copied in the range E4:G6.

Cell D4 contains the formula =D1-D2. This means that It contains the formula generated from the values of two cells, one that is three rows up in the same column and the other that Is two rows up in the same column. When D4 is copied, the formula will be adjusted so that the cell references in the target cells have the same relationship.

For example, F6 contains the formula =F3-F4., which refers to a formula that is three rows up in the same column and two rows up in the same column from F6.

Absolute cell referencing for a range of cells

Now note what happens when absolute cell referencing is applied.

SCREENSHOT HERE

As this screen shows the addresses in the target cells are identical to that in the source cell.

Mixed cell referencing

You may make either the column or row absolute. When you do this the column or row reference will remain fixed when the cell is copied. This is referred to as mixed cell referencing

The following two screens illustrate the procedure for this third type of referencing.


SCREENSHOT HERE


SCREENSHOT HERE

Alternative terms used for these three types of referencing are relative addressing, absolute addressing and mixed addressing.

Retrieved from "https://wikieducator.org/index.php?title=CCNC/CCNC_Module_4/Working_with_Formulas_and_Functions/Cell_referencing&oldid=90800"