Forum Discussion

packie's avatar
packie
Brass Contributor
Jun 16, 2024

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.

 

 

  • 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.

    • packie's avatar
      packie
      Brass Contributor
      I have taken another look at your 2nd option and I will be able to use it after all. Thank you.
    • packie's avatar
      packie
      Brass Contributor
      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?

Resources