How to make a spreadsheet column-formula with relative cell addresses?

Question

Level 1

0 points

How to make a spreadsheet column-formula with relative cell addresses?

I'm trying to make a spreadsheet compute with relative cell addresses. I tried what's on the help page, and it doesn't work.

What I’m attempting is to make one column contain the differences of another column, like,

Gn=Fn-F(n-1)

to apply to the entire G column, without typing (or even pasting) 100s of individual formulas. As an added bonus, I'd like for the invalid cell address resulting from n=1, to be defaulted to 0.

How to do this???

iMac, OS X Mavericks (10.9), 27-in, mid-2011, 3.1GHz Core i5

Posted on Nov 11, 2013 12:12 AM

Reply

Answer 1

Nov 11, 2013 6:33 AM in response to Fred S

Fred,

1) add a column header

2) in cell G2 enter the fomula

"=F2−SUM(F1)" without the double quotes

3) select G2, copy

4 select column G, then hold the command key and click G1 (to unselect it), paste

Reply

Answer 2

Nov 11, 2013 7:04 AM in response to Wayne Contello

Another way to move the relative formula to other cells:

Select the cell with the formula, use the little handle in the center of the side (it appears when you move your pointer there). In the picture it is in the center of the bottom side. Drag that handle downward, this copies the formula into the cells you include in the drag. You can also do this upward, leftward, rightward by selecting one of the other sides.

Reply