How Cell References Automatically Adjust in Numbers
When you copy and paste a formula in Numbers it doesn't just paste an exact duplicate of the formula. Instead, it adjusts the cell references based on the locations of the original and the new location. Learn why and how it does this, and how to modify this behavior to fir your needs.
Want to know more about how to use Numbers on your Mac? Check out this MacMost course!
Comments: 9 Responses to “How Cell References Automatically Adjust in Numbers”
Alan Chambers
1 year ago
Hi Gary, I have been using numbers for iMac for a while now and I thought I was doing OK. That was before viewing your video . In the past in numbers I could copy and paste but now I am using headers and footers copy and paste will not work for selected figures in the footer. Is it not possible to do so?.
Alan: Not sure what you are doing. What are you copying exactly, from where exactly, and where are you pasting it? Once you paste, what is not as you expected?
Charles Dunbar
1 year ago
Numbers preserving rows: Hi, Have three columns containing digits, a column that keep a running average (G2) and one column/cell (J2) keeping a count of the rows in column (C2).
I use this formula to calculate the running average SUM(C$2:C26)÷J$2
and this column (J2) to count the number of row (COUNTIF(C$2:C25,">0"))
the problem is this: When the next row calculates the running average - all the running averages above take on that value. What am I missing? Thanks.
So J2 doesn't related to row 2, just a count of all rows in the table?
Why not just use AVERAGE(C$2:C25) for the average?
charles dunbar
1 year ago
Gary, thanks for the input but the result is still the same. I'm trying to keep the previously calculated rows in the column from taking the same value. How do I protect the cells that have been completed from changing? My calculations are good.
Charles: Examine your cells carefully. In J15 for instance it should be AVERAGE(C$2:C15) and then in J15 it should be AVERAGE(C$2:C16) and so on. Copy and paste should take care of that for you as long as you are starting off right.
charles dunbar
1 year ago
Gary, I'm not making myself clear. i'll try again: ex. C2 thru C6 each contains a diff val. d2 thru d6 each calculate a RUNNING average for for the cumulative val of rows to that point ex. c2 thru c4. But when ANY D row after the first (d2) is calculated all the the D rows ABOVE take on that value erasing what was already there. Another ex.: d2 has calc. value of 122. when d3 is calculated to 155, d2 will then also show 155 - and so-on. It's a poser. Many thanx for you attempts to help me.
Charles: So if d4 is the average of c2 to c4, then it should be =AVERAGE(C$2:C4) is that what it is? If not, what do you have in there? Then, copy and paste d4 into d5. Now look in d5. Is it =AVERAGE(C$2:C5)? It should be. And d4 should still be =AVERAGE(C$2:C4). Is it?
Charles Dunbar
1 year ago
Gary, you are correct. I could not get your method to work in my sheet so finally I just rebuilt the sheet from scratch and used your method of calculation, it worked! I have no idea what was hidden in the old sheet. Thanks for your efforts in helping me.
Charles
Hi Gary, I have been using numbers for iMac for a while now and I thought I was doing OK. That was before viewing your video . In the past in numbers I could copy and paste but now I am using headers and footers copy and paste will not work for selected figures in the footer. Is it not possible to do so?.
Alan: Not sure what you are doing. What are you copying exactly, from where exactly, and where are you pasting it? Once you paste, what is not as you expected?
Numbers preserving rows: Hi, Have three columns containing digits, a column that keep a running average (G2) and one column/cell (J2) keeping a count of the rows in column (C2).
I use this formula to calculate the running average SUM(C$2:C26)÷J$2
and this column (J2) to count the number of row (COUNTIF(C$2:C25,">0"))
the problem is this: When the next row calculates the running average - all the running averages above take on that value. What am I missing? Thanks.
So J2 doesn't related to row 2, just a count of all rows in the table?
Why not just use AVERAGE(C$2:C25) for the average?
Gary, thanks for the input but the result is still the same. I'm trying to keep the previously calculated rows in the column from taking the same value. How do I protect the cells that have been completed from changing? My calculations are good.
Charles: Examine your cells carefully. In J15 for instance it should be AVERAGE(C$2:C15) and then in J15 it should be AVERAGE(C$2:C16) and so on. Copy and paste should take care of that for you as long as you are starting off right.
Gary, I'm not making myself clear. i'll try again: ex. C2 thru C6 each contains a diff val. d2 thru d6 each calculate a RUNNING average for for the cumulative val of rows to that point ex. c2 thru c4. But when ANY D row after the first (d2) is calculated all the the D rows ABOVE take on that value erasing what was already there. Another ex.: d2 has calc. value of 122. when d3 is calculated to 155, d2 will then also show 155 - and so-on. It's a poser. Many thanx for you attempts to help me.
Charles: So if d4 is the average of c2 to c4, then it should be =AVERAGE(C$2:C4) is that what it is? If not, what do you have in there? Then, copy and paste d4 into d5. Now look in d5. Is it =AVERAGE(C$2:C5)? It should be. And d4 should still be =AVERAGE(C$2:C4). Is it?
Gary, you are correct. I could not get your method to work in my sheet so finally I just rebuilt the sheet from scratch and used your method of calculation, it worked! I have no idea what was hidden in the old sheet. Thanks for your efforts in helping me.
Charles