CODEWITHSUNDEEP
excel
Anthony Bane
Anthony Bane

Reputation: 75

How do I stop excel updating cell references upon insertion of new data

I have a spreadsheet which takes in certain parameters that I update every day. This is then inserted into a separate worksheet which holds all inserted data. I then use this data to take an average of the latest 5 entries to smooth out day to day variation: e.g.

=((B1*(100-(AVERAGE(Data!G$2:G$6))))/(343.805764))

When I insert a new data line, my formula updates to:

=((B1*(100-(AVERAGE(Data!G$3:G$7))))/(343.805764))

How can I prevent this update?

Upvotes: 2

Views: 118

Answers (1)

Sebastian B
Sebastian B

Reputation: 451

If you really need to "fix" a range - regardless of the default behaviour of Excel to change row-numbers if you insert new rows - try working with hard-coded ranges using INDEX

=((B1*(100-(AVERAGE(INDEX(Data!G:G,2):INDEX(Data!G:G,6)))))/(343.805764))

Upvotes: 2

Related Questions