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

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

Best reply

Barry

Level 9

59,891 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

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

Similar questions