Forum Discussion
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:...
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.
- I have taken another look at your 2nd option and I will be able to use it after all. Thank you.
- 1st 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?
- I am not sure what you mean, could you elaborate a bit more please.
