COMPUTATIONS IN A FORMULA

The cell references in the “pasted on” cell depend on

The dollar sign in the The copy &

the location of the dollar signs in the formula in the

“copied from” cell paste action

original, “copied from” cell

Reference behavior

Figure: 24: Only the reference to “C” does not adjust

with a dollar sign

Copy F219 because only “C” has a dollar prefix

before one of the

and paste

column references

into G220.

Original cell:

F219 = $C219 + D219

Reference behavior Figure 25: Only the reference to “219” (in the formula

with a dollar sign part “C$219”) does not adjust because only that “219”

before one of the row has a dollar prefix

Copy F219

references

and paste

into G220.

Original cell:

F219 = C$219 + D219

The part “D219” adjusts to “E220” to adjust for the fact that the “pasted on” cell is one

5

column to the right (so “D E") and one row below (so “219 220”.)

40

Chapter 2: Copying/Cutting and pasting formulae

The cell references in the “pasted on” cell depend on

The dollar sign in the The copy &

the location of the dollar signs in the formula in the

“copied from” cell paste action

original, “copied from” cell

Reference behavior

with a dollar sign Figure 26: the references to “C,” “D” and to “219” (in

before all but one of the formula part “$D$219”) do not adjust because they

the row/column all have a dollar prefix

Copy F219

references

and paste

into G220.

Original cell:

F219 = $C219 +

$D$219

Original cell:

Copy F219 Try it¦

and paste

F219 = $C$219 +

G220 = $C$219 + $D$219

into G220.

$D$219

Original cell:

Copy F219 Try it...

and paste

F219 = $C219 +

G220 = $C220 + $D220

into G220.

$D219

Original cell:

Copy F219 Try it...

and paste

F219 = C219 +

G220 = D220 + $D$219

into G220.

$D$219

COPYING AND PASTING FORMULAS FROM ONE

2.5

WORKSHEET TO ANOTHER

The worksheet “second” in the sample data file has the same data as the

worksheet you are currently on (“main.”) In the worksheet main, select

the cell F219 and choose the menu option EDIT/COPY. Select the

worksheet “second” and paste the formula into cell F219. Notice that the

formula is duplicated.

41

Statistical Analysis with Excel

PASTING ONE FORMULA TO MANY CELLS,

2.6

COLUMNS, ROWS

Copy the formula. Select the range for pasting and paste or “Paste

Special” the formula.

PASTING SEVERAL FORMULAS TO A SYMMETRIC

2.7

BUT LARGER RANGE

Assume you have different formulas in cells G2, H2, and I2. You want to

paste the formula:

” In G2 to G3:G289

” In H2 to H3:H289

” In I2 to I3:I289

Select the range G2:I2. Pick the menu option EDIT/COPY. Highlight the

range G3:I289. (Shortcut: select G3. Scroll down to I289 without

touching the sheet. Depress the SHIFT key and click on cell I289.) Pick

the menu option EDIT/PASTE.