Dynamically change formula when new data is added

Brass Contributor

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.

 

 

5 Replies

@packie 

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.

1st option:
=AVERAGE(J10:J1000)
this doesn't work because each cell in J contains a formula

2nd & 3rd options
as above






@packie 

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.