Forum Discussion
packie
Jun 16, 2024Brass Contributor
Dynamically change formula when new data is added
Hi,
Each week I manually change the following formula by adding an additional cell to the range:
=AVERAGE(J10:J28)
At the moment I am changing the formula manually to:
=AVERAGE(J10:J29)
next week I will change it to:
=AVERAGE(J10:J30)
and so on......
is there a way to do this without having to manually do this?
Thank you for your interest.
Since AVERAGE ignores empty cells and cells with text values, you might use
=AVERAGE(J10:J1000)
Another option is to use a formula such as
=AVERAGE(J10:J29)
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.
- packieBrass ContributorI have taken another look at your 2nd option and I will be able to use it after all. Thank you.
- packieBrass Contributor1st option:
=AVERAGE(J10:J1000)
this doesn't work because each cell in J contains a formula
2nd & 3rd options
as aboveHow 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?