Google Sheets : Automatically include new added cells to an existing formula-CodePudding

I have a simple question for which I have failed to find an answer, simple or otherwise!

My Spreadsheet looks like this (say)

   A          B       C     
1  Name     Amount    100    
2  Neil      50    
3  Rose      100  
4  Alex      150

Now the cell C1 contains the formula =Average(B2:B). The problem arises when i add a new row ABOVE row 2. The existing formula in C1 gets changed to =Average(B3:B) and remains linked to the original cell as it gets pushed down, which is not what i want.

There must be a way, so I ask you, the internet for your assistance :-)

CodePudding user response：

use:

=AVERAGE(INDIRECT("B2:B"))