Hello,
I have a range of cells named "one" (for example A1:A5 but the length is variable).
How can I add all values in the range except the last one?
The named range is a column (A) with a variable number of rows, and the last row of the named range "one" is the sum of the range. In order to avoid the circular reference I have to sum all values in the range without the last row... something like:
=SUM(one - last_row)
How can I make the SUM of the range without last row in the range?
Last edited by alexandruc; 10-27-2010 at 09:06 AM.
Why include the total in the range?
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
afaik i don't think you can.if its a dynamic named range you will always get a circular reference if sum() is included at the end. take the sum out and put it at the top outside the named range instead
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Originally Posted by royUK
Ok.. I have a range A1:A3 and a total on the A4 sum(A1:A3).
By pressing a button the VB inserts a new row above the total. So the total A4 becomes A5.
Now the total on A5 will not add the newly inserted row. So A5 will be sum(A1:A3). I need the total to include also the newly inserted rows.. as many as they may be.
For this reason I use a named range which includes the total so that when a new row is inserted above the total it will become part of the named range.
The problem is that I don't know how to remove from the sum formula the last row in the named range (i.e the total)...
I could use a blank row above the total and insert the new row above the blank row so that it will be part of the named rage... but I wandered it could be done without this blank row.
Perhaps:
=SUM(OFFSET(one,,,ROWS(one)-1,1))
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Excelent!!! Thank you very much!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks