sum to recognize new rows

[deb]

I have a column that I average the sum (ignoring blank cells in the formula)
sum of column, divided by count of cells that are not blank.

here is the formula that is in B10...
=(SUM(B4:B9))/COUNTA(B4:B9)

When I insert a row above B10 the formula does not change to incorporate
that additional row. How can I get the formula to recognize new rows?
should now be
=(SUM(B4:B10))/COUNTA(B4:B10)

Thanks

 

[joemeshuggah]

you could copy row 10 and paste to row 11, and then type your new values over
those in row 10

 

[joemeshuggah]

....forgot to mention you would need to also add $ signs to b4 (b$4)

 

[FSt1]

hi
you could create a dynamic range.
2003 on the menu bar>Insert>name>define
in the name in workbook box, enter a name for the range say myname.
then in the refers to box enter...
=OFFSET($B$1,0,0,MATCH(99999999,$B:$B),1)

click the add button the ok out.
then on the sheet use this formula...

=sum(myrange)/Counta(myrange)

the range will adjust as you add rows (or delete rows)

regards
FSt1