This seems like it shouldn't be too tough, but it has me stumped.
I have numbers in A1 through A10, and a formula in A11 = SUM(A1:A10). If I insert a new row at row 10, the formula does not expand to include it.
How can the formula be written so as to include the new row? And I will need to copy it to other columns, so it would be best if it automatically adjusted to the column it is in.
Hi,
If your cursor is in row 11 when you add a new row then the A1:A10 range isn't affected and hence doesn't adjust.
Simplest solution is to have a blank row 11 and in A12 enter the formula =SUM(A1:A11). Then with the active cell in row 11 when you insert a row the range will adjust.
Or alternatively put your SUM formula above the data and cover a suitably large range. In general I prefer total rows above data since they can then remain fixed.
Regards
Last edited by Richard Buttrey; 02-09-2012 at 08:12 PM.
Richard Buttrey
If this was useful then please rate it appropriately.
Click the small star iconat the bottom left of my post.
OK, thanks much. I don't have the option though of using a blank row or putting the sum above the data in this application. I played with it a bit more and came up with:
=SUM(OFFSET(A1,0,0,ROW()-ROW(A1),1))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks