Copper Contributor

Using an average formula =average (d4:h4)for 5 cells in a row then delete d4 shift cells left formula changes to = average(d4:g4) , how can I stop this changing?

4 Replies



You can use =AVERAGE(INDIRECT("D4:H4"))


Getting value error 

There are various way but I would ask WHY. Is this something you are doing a lot in general or something for a particular case? For example you could use INDIRECT("D4:H4") but I try to avoid using that. You could use INDEX(4:4,{4,5,6,7,8}). But as a general solution either or both of those may be very burdensome. Maybe the better answer is an alternative to not delete the cell but shift the data.


Probably best to clear contents rather than delete but here's an option: