Dynamically change formula when new data is added

Brass Contributor



Each week I manually change the following formula by adding an additional cell to the range: 




At the moment I am changing the formula manually to: 




next week I will change it to:




and so on......


is there a way to do this without having to manually do this?


Thank you for your interest.



5 Replies


Since AVERAGE ignores empty cells and cells with text values, you might use




Another option is to use a formula such as




when the data are in J10:J28. When you need to add new data, insert a new row in row 29. Excel will automatically update the formula to =SUM(J10:J30)


Yest another option is to convert your range to a table. When you add a new row to the table, Excel will automatically adjust the formula.

1st option:
this doesn't work because each cell in J contains a formula

2nd & 3rd options
as above


How the data is structured, any logic how formulae in J10:J30 are separated from another formulae in column J?

And where the AVERAGE shall be placed, also in column J or in some other place?

I am not sure what you mean, could you elaborate a bit more please.
I have taken another look at your 2nd option and I will be able to use it after all. Thank you.