+ Reply to Thread
Results 1 to 4 of 4

Use a variable size range without VBA

  1. #1
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Use a variable size range without VBA

    I can do this easily with VBA, but wish not to. Also, if at all possible, no array formulas either:

    I want to have value in A1 that determines the "number of elements" or "size of range".
    If it's 2, I want sum(A3:A4)
    If it's 3, I want sum(A3:A5)
    If it's 8, I want sum(A3:A10)

    I've been messing with offset and index but haven't cracked it. For the above middle example of A1=3:
    For starters, I looked at something like offset(A3:A3,A1-1,0) which (unsuitably) is A5:A5, not A3:A5.

    I came up with a novel angle, taking the maximum possible range (assume never over 10 elements) and going something like
    sum(A3:A12) - sum(A5:A12)
    which looks like:
    sum(A$3:A$12) - sum(offset(a3:a$12,a1,0))

    That seems to work, but that A12 $ anchor is not immune to the offset. It (unsuitably) sums A5:A14.
    It's okay only if I ensure that A13:A21 are zero or nonnumeric, a rude restriction.
    (A further hokey workaround solution to that is to subtract SUM(A13:A21) but I feel I'm already over my hokey limit, you know?)

    Is there something better? Can I "apply an offset to one end of a range" ?

    P.S. I can get there handily with a helper column. However I sense that one of you will come up with a "proper" tight answer

  2. #2
    Valued Forum Contributor
    Join Date
    06-02-2012
    Location
    London, England
    MS-Off Ver
    365
    Posts
    397

    Re: Use a variable size range without VBA

    Maybe this?

    =SUM(A3:INDEX(A:A,A1+2))

    Beth.

  3. #3
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: Use a variable size range without VBA

    Yes, that! I didn't think I could operate on "one end of the range" but that sure does so! Way to go.

  4. #4
    Valued Forum Contributor
    Join Date
    06-02-2012
    Location
    London, England
    MS-Off Ver
    365
    Posts
    397

    Re: Use a variable size range without VBA

    Happy to help :D

    Beth.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Size a range from the size of another in VBA variable
    By jayherring86 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-09-2015, 09:17 AM
  2. Replacement of Indirect Function for Variable Size Range
    By Andrew Blundon in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-01-2013, 08:55 AM
  3. Average excluding zeros over a variable range of fixed size
    By tface in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-09-2013, 08:23 AM
  4. vlookup with variable range and variable array size
    By chaslie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-16-2013, 02:37 PM
  5. Establishing a variable range based on data size
    By Bjordion in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-07-2011, 10:29 AM
  6. Establishing a variable range based on data size
    By Bjordion in forum Excel General
    Replies: 2
    Last Post: 10-05-2011, 04:57 PM
  7. Replies: 3
    Last Post: 06-26-2010, 02:43 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1