Results 1 to 3 of 3

Dynamic Range Reference, Varying Size of Range

Threaded View

cdiaz Dynamic Range Reference,... 06-30-2011, 02:41 PM
bentleybob Re: Dynamic Range Reference,... 06-30-2011, 02:47 PM
cdiaz Re: Dynamic Range Reference,... 06-30-2011, 02:49 PM
  1. #1
    Registered User
    Join Date
    06-02-2011
    Location
    Arizona, United States
    MS-Off Ver
    Excel 2007
    Posts
    14

    Dynamic Range Reference, Varying Size of Range

    Hello!

    I would like to reference a range in a sum formula, but the range size depends on some other number called "months left". I have a formula that does this, but I am wondering if there is an easier, more efficient, way.

    A very simplified example: my range with numbers is A2:A6, if month left is 1, I want my formula to return A2, if months left is 2, I want my formula to give me A2+A3, if months left is 3, I want my formula to give me A2+A3+A4

    Here's my approach:
    =SUM(IF(MonthsLeft=1,A2,IF(MonthsLeft=2,A2:A3,IF(MonthsLeft=3,A2:A4,IF(MonthsLeft=4,A2:A5,A2:A6)))))
    and so on, for up to 12 months.

    This does what I want, but can you tell me a nicer way? Is a nicer way even possible? As this is mostly out of curiosity and desire to learn, don't rack your brain, I don't mind if I don't get an answer.

    I added an attachment.

    Thanks. **
    Attached Files Attached Files
    Last edited by cdiaz; 06-30-2011 at 02:50 PM.

Thread Information

Users Browsing this Thread

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

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