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.

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?.

    1 year ago

    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
    9 months 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.

    9 months ago

    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
    9 months 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.

    9 months ago

    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
    9 months 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.

    9 months ago

    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
    9 months 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

Comments Closed.