Google-sheets – How to lock formula when adding column in Google Spreadsheets

google sheets

I’m trying to keep an average of the 5 newest values of a data set: Average($d$1:$h$1). If I add a new column to the left of D for new data, the formula changes to ($e$1:$i$1). How do I prevent the formula from changing?

Best Answer

Please try: 

=average(offset(A1,0,4,1,5))

(But don't add a column to the left of D that is to the left of A also!)

Help with =offset, and other functions, is available via Help and Function list.

Related Topic