New Contributor

# How do I keep a formula from changing when I advance the data?

I keep daily financial data in a horizontal table. Every day I insert a column on the left and add that day's prices. I want to have a spreadsheet that will calculate the average of the last  5 days. But if I make a formula AVERAGE(E5:I5), when I insert the new data the formula will automatically change to AVERAGE(F5:J5). I want to keep it averaging E5:I5. Even if I do AVERAGE(\$E\$5:\$I\$5) it doesn't help. I tried using a range but that automatically adjusts, too. Is there anything I can do to keep averaging the same 5 columns?

3 Replies

# Re: How do I keep a formula from changing when I advance the data?

@Marshall1954 Let's say you want to returns the last 5-day average in D5, try this:

``=AVERAGE(OFFSET(D5,0,1,1,5))``

# Re: How do I keep a formula from changing when I advance the data?

Good thinking and nice formula for finding the average but no, that changes too - the D5 just changes to E5 when I input the new data. Even if I add the \$ sign in it.

# Re: How do I keep a formula from changing when I advance the data?

It depends where do you insert new column, after the column D or not. You may anchor to column A  slightly modifying the formula - if only new column is not before A.