Add row, include new row in formula

[Carson Dyle]

I have a simple column of numbers, and below them a row that computes the average. Each month I add a new row. The formula, however, stay referencing the previous last row. How do I get it to include the newly added row? Is it as easy as changing the range reference, or do I need to use offset() or indirect() or somesuch?

See the attached example.

[Richard Buttrey]

Personally, and now we no longer use traditional pencil and lined paper to tot up numbers I always have stuff like totals above the data in question.

That way you can simply set the formula to average the greatest number of cells you're ever likely to have.

Otherwise you could add two blank rows 16 & 17, have the total average B2:B17 and hide row 17. Then when you choose the blank row 16 to insert another row the total will adjust.

You could use stuff like OFFSET but frankly that's taking the proverbial sledgehammer to do you know what.

[Carson Dyle]

A hidden row sounds like an easy solution. Or maybe get out the sledgehammer.