Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Formula (2002/SP3)

    Hi,

    In the example that I've attached, I'm trying to figure out a formula that would give me the sum of the last X numbers starting from the bottom of the list. Can I do this using an Excel formula, or do I need to use VBA?
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (2002/SP3)

    Assuming your table always ends in row 26:
    =SUM(OFFSET(C26,0,0,-G6,1))

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (2002/SP3)

    If the row is variable you can use:
    =SUM(OFFSET(INDIRECT("C"&MATCH(E3+1000,C:C,1)),0,0 ,-G6,1))

  4. #4
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (2002/SP3)

    Thank you Mike.

    Two questions:
    1) What does MATCH(E3+1000,C:C,1) accomplish? ie, what is E3 for?
    2) Can you explain to me what does the INDIRECT function do in this case?

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (2002/SP3)

    <hr>What does MATCH(E3+1000,C:C,1) accomplish? ie, what is E3 for?<hr>
    That, unfortunately is a double typo. It should have been MATCH(3E+100,C:C,1)

    =SUM(OFFSET(INDIRECT("C"&MATCH(3E+100,C:C,1)),0,0,-G6,1)) broken down:

    Match(3E+100,C:C,1) returns the last row with a number in it.
    indirect("C"&MATCH(3E+100,C:C,1)) sets the beginning cell for the OFFSET function.
    OFFSET(INDIRECT("C"&MATCH(3E+100,C:C,1)),0,0,-G6,1) creates a range starting in the last row in the C column that is as tall as the value in G6.
    Sum() adds the range created using the Offset function.

  6. #6
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (2002/SP3)

    Thanks for the great help. :-)

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Formula (2002/SP3)

    Or try……

    =SUM(OFFSET(C1,MATCH(9.99E+307,C:C)-1,0,-G6))

    Regards
    Bosco

Posting Permissions

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