Hi guys,
I have a fairly simple sheet which I use to calculate the change in value by percentage during a period of time. For example at the start of the period the value was 40,475.64 and at the end of the period it was 42,104.62 a change of 4.02%. (Currently 70 rows of data but growing every day) I am happy with the way the calculation works but each day I have to change the formula reference to point at the new (next) row of data in order for the new percentage to be calculated. Is there a way to automatically change the percentage result each time a new row of data is added?
I am a retired engineer now in my 70’s and enjoy keeping my brain active by trying to solve problems, to make my life easier, in this case trying to track the change in percentage of my pension. I can get it to work perfectly by me manually making the reference cell change every day but it would be so much easier if when adding a new row of data the new percentage is automatically calculated. This is only one section of a larger spreadsheet and if I can understand how to do it I can make the changes in other areas. Thank you
—–
Ian Berry
Pay careful attention to the "Preserve Row" and "Preserve Column" properties of cell references. In the formula, these are usually represented with a $ before the reference.
So if E2 is the first value of the period, and you have rows that go to E71, then the formula in E3 should be:
=(E3-E$2)/E$2
Then when you add row 4, it should carry that forward in E4 as:
=(E4-E$2)/E$2
Notice how the $2 stays the same, but the 3 changes to a 4.
See https://macmost.com/how-cell-references-automatically-adjust-in-numbers.html
Thank you. That is exactly how i currently, do it. Perhaps I didn’t explain properly. The percentage result appears in the footer of the table and calculates the result of the period start and the current figure as shown in your response, but I have to change the result formula every time I add a row so that it points at the correct cell. I am trying to find a way for the result to calculate based on the reference cell in each new row added so I don’t need to manually change it myself.
Ian: It should work the same in the footer if you apply the "Preserve Row" property properly. But I'm missing some part of this here. Maybe if you gave a specific example with specific column rows for the formula I would understand better what you mean by "reference cell" and such.