You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

User profile for user: rojapie
rojapie Author
User level: Level 1
8 points

How do I shift data down without changing the formula of another cell?

Not entirely sure how to word what I'm asking so here it goes.


I'm making a budget spreadsheet and on one table, every pay check is entered net and gross. On another table, I have two cells that take a six month average of both net and gross. What I'm trying to accomplish is being able to add a new line every week with updated pay to the top of the table and the formula that takes the averages doesn't change and basically "kicks out" the oldest data and replaces it with the newest.

The cells in the formula are B2:B27 and when I add new data, the formula changes to B3:B28. Any advice would be helpful, thanks.

Posted on Mar 9, 2021 3:17 PM

Reply
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
59,951 points

Posted on Mar 9, 2021 4:15 PM

OFFSET is your friend here.


Here is a sample table with a set of values from 74 to 100 in column B.


To its right is a single column table witht he same row height and numbers counting the rows containing values to be included in the average. This column is here for reference purposes only, and plays no part in the calculations.


The third table represents the 'two column table" reporting the moving average of the 26 most recent cheques recorded in column B of Table 1.


The second image, below, shows the same three tables after three new cheques have been recorded in three new rows inserted at the top of Table 1. To ensure a sizeable change in the 'last 26' average, I made these entries significantly less that the others.


The formula, entered in A2 of the small table is shown below:



AVERAGE(OFFSET(Table 1::B1,1,0,26,1))


Syntax: OFFSET(base, row-offset, column-offset, rows, columns)


Regards,

Barry

View in context

Similar questions

3 replies
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
59,951 points

Mar 9, 2021 4:15 PM in response to rojapie

OFFSET is your friend here.


Here is a sample table with a set of values from 74 to 100 in column B.


To its right is a single column table witht he same row height and numbers counting the rows containing values to be included in the average. This column is here for reference purposes only, and plays no part in the calculations.


The third table represents the 'two column table" reporting the moving average of the 26 most recent cheques recorded in column B of Table 1.


The second image, below, shows the same three tables after three new cheques have been recorded in three new rows inserted at the top of Table 1. To ensure a sizeable change in the 'last 26' average, I made these entries significantly less that the others.


The formula, entered in A2 of the small table is shown below:



AVERAGE(OFFSET(Table 1::B1,1,0,26,1))


Syntax: OFFSET(base, row-offset, column-offset, rows, columns)


Regards,

Barry

Reply

How do I shift data down without changing the formula of another cell?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up