Excel - Inserting a row within a range that is defined in a sum function

Asked By Victor on 25-Nov-09 09:41 AM
I have the following question

When  i have a sheet with the following data:

cell A1        1
cell A2        2
cell A3        3
cell A4   =sum(A1:A3)  ==> 6

When i want to insert in row 4 another value (let us say):
cell A4        4

Then in cel A5 is still the formula =sum(A1:A3).

Is there a trick to let the formula change (automattically, e.g. with a key
combination) when inserting a row into  ==>  =sum(A1:A4)

I am using Office Excel 2003

Daryl S replied to Victor on 25-Nov-09 10:58 AM
Victor -

Put your sum in cell A5 with =SUM(A1:A4) , leaving row 4 blank.

Now you can add a row (select row 4 and Insert Row), and the formula will
adjust with the inserted rows.  This is because when you insert a row in
Excel, it inserts the row above the current row.  Since the 'current row' in
your example was the sum row, it did not include that row in the formula, and
the formula was not updated.  In the method above, the current row (row 4)
would be in the formula in cell A5, and it will be adjusted when you insert
the new row.
Daryl S
John replied to Victor on 25-Nov-09 11:32 AM
HI Victor
You can use the function " List " goto > Data > List
You select your column excluding the total and it will adjust by itself.