HELP --"flexible" formula? - Page 2
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: HELP --"flexible" formula?

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    On 2002-02-26 19:26, Anonymous wrote:
    On 2002-02-26 19:22, Anonymous wrote:
    On 2002-02-26 19:07, Derek wrote:
    Hi
    If you still wanted the total below your sum, you could type A2 in cell A1 (or any other cell you choose) and refer to it indirectly like this.

    =SUM(INDIRECT($A$1):A7)

    Regards
    Derek
    I don't follow that. The ref A7 in the formula does not change if a row is inserted immediately above row 8.
    The formula suggested by Autolycus (entered in A8) automatcally adjusts if row(s) are inserted immediately above the cell with the formula.
    Oops! Apologies all, you are right it does not work if a row is inserted immediately above it. I will go and stand in the corner for 10 minutes,
    Regards
    Derek

  2. #12
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-02-26 18:57, Autolycus wrote:
    Why not just use :-

    =SUM(A2:OFFSET(A8,-1,0))
    A close reading of Lindsay's post implies that there can be additions/deletions before and after the row of formula, whence I opted for moving the formula to the top in the column of interest.

  3. #13
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-02-26 18:48, anno wrote:
    oops. forgot that you can't use a dynamic range if the formula that uses it is in the same column. aladin's is a much better idea, although i don't quite get what the match function is doing.

    [ This Message was edited by: anno on 2002-02-26 18:51 ]
    anno,

    There have been a few occasions/discussions at the old board (see Archives) what MATCH is doing in setups similar to the one in this thread & and recently at this board

    http://mrexcel.com/board/viewtopic.p...ic=567&forum=2

    where I provided a short explanation.

    Regards,

    Aladin

  4. #14
    Guest

    Default

    On 2002-02-27 01:35, Aladin Akyurek wrote:
    On 2002-02-26 18:57, Autolycus wrote:
    Why not just use :-

    =SUM(A2:OFFSET(A8,-1,0))
    A close reading of Lindsay's post implies that there can be additions/deletions before and after the row of formula, whence I opted for moving the formula to the top in the column of interest.
    My close reading implies otherwise. Nowhere does he say that rows will be added below the formula, but rather (the capitals are mine) :-

    In his first post "I have a formula that I would like to change as rows are added ABOVE it."

    In his second post "If I have sum(A2:A11) in cell A12 and then I insert a row ABOVE row 12, the formula stays sum(A2:A11). If I add rows further up in the spreadsheet, the formula changes like I want, but rows are always added at the bottom, so that doesn't help me." - by "bottom" he obviously means the bottom of the rows being summed, i.e. the row immediately above the formula

  5. #15
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-02-27 01:57, Anonymous wrote:
    On 2002-02-27 01:35, Aladin Akyurek wrote:
    On 2002-02-26 18:57, Autolycus wrote:
    Why not just use :-

    =SUM(A2:OFFSET(A8,-1,0))
    A close reading of Lindsay's post implies that there can be additions/deletions before and after the row of formula, whence I opted for moving the formula to the top in the column of interest.
    My close reading implies otherwise. Nowhere does he say that rows will be added below the formula, but rather (the capitals are mine) :-

    In his first post "I have a formula that I would like to change as rows are added ABOVE it."

    In his second post "If I have sum(A2:A11) in cell A12 and then I insert a row ABOVE row 12, the formula stays sum(A2:A11). If I add rows further up in the spreadsheet, the formula changes like I want, but rows are always added at the bottom, so that doesn't help me." - by "bottom" he obviously means the bottom of the rows being summed, i.e. the row immediately above the formula
    No need to defend anything here. My comment had underlying appreciation/approval of the formula Autolycus suggested. My post has/had non-critical intent to communicate, directed to Autolycus, why I chose a different path. Hope you're not going to apply hermeneutics to what I actually wrote and simply accept the correction I just gave.

    Regards,

    Aladin

  6. #16
    Guest

    Default

    Apply hermeneutics? What me? Certainly not.
    (And not exegesis either.)

  7. #17
    New Member
    Join Date
    Feb 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Wow! Thanks for all the input on this problem, everybody. To clarify: rows are added ONLY ABOVE the formula. However, what goes on above the formula is a big, big mess -- additions, deletions, and FREQUENT sorting of the rows are all very common. I am not now in work, but I will try =SUM(A2:OFFSET(A8,-1,0)) when I get in. With this additional information, would this formula seem to work? And, could someone please explain what OFFSET(A8,-1,0) means?

    Thanks so much,
    Lindsay

  8. #18
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-02-27 07:18, chezlinds wrote:
    Wow! Thanks for all the input on this problem, everybody. To clarify: rows are added ONLY ABOVE the formula. However, what goes on above the formula is a big, big mess -- additions, deletions, and FREQUENT sorting of the rows are all very common. I am not now in work, but I will try =SUM(A2:OFFSET(A8,-1,0)) when I get in. With this additional information, would this formula seem to work? And, could someone please explain what OFFSET(A8,-1,0) means?

    Thanks so much,
    Lindsay
    The cell immediately above A8.

  9. #19
    New Member
    Join Date
    Feb 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    UPDATE: The SUM(A2:OFFSET(A8,-1,0)) formula proposed by Autolycus works a treat. Thanks so much everyone.

    Lindsay

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com