Forum Discussion

Cookbailey's avatar
Cookbailey
Copper Contributor
Dec 06, 2023

Formulas

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?

Formulas and Functions

4 Replies

Sort By
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Dec 06, 2023

    Cookbailey 

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

     

    =AVERAGE(TAKE($D$4:$ZZ$4,,5))
  • mtarler's avatar
    mtarler
    Silver Contributor
    Dec 06, 2023
    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.

Resources