+ Reply to Thread
Results 1 to 4 of 4

subset of range defined by start date and date date in dedicated cells

  1. #1
    Registered User
    Join Date
    07-20-2015
    Location
    Austin, TX
    MS-Off Ver
    2010
    Posts
    2

    subset of range defined by start date and date date in dedicated cells

    I want to use a variable start date and end date in two dedicated cells as input into formulas which isolate a subset of a range and return results based only on values within that date range. (see attached)

    Sorry if this is excruciatingly easy for you alpha geeks.

    THANK YOU!
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: subset of range defined by start date and date date in dedicated cells

    You can use this array formula for the MIN.

    =MIN(IF((DateRange>=$E$3)*(DateRange<=$F$3),ValueRange))

    Then just change the word MIN to MAX and AVERAGE for the other 2.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,921

    Re: subset of range defined by start date and date date in dedicated cells

    G3=LARGE(INDEX((DateRange>=$E3)*(DateRange<=$F3)*(ValueRange),0),COUNTIFS(DateRange,">="&$E3,DateRange,"<="&$F3))

    H3=LARGE(INDEX((DateRange>=$E3)*(DateRange<=$F3)*(ValueRange),0),1)

    I3=AVERAGEIFS(ValueRange,DateRange,">="&$E3,DateRange,"<="&$F3)
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Registered User
    Join Date
    07-20-2015
    Location
    Austin, TX
    MS-Off Ver
    2010
    Posts
    2

    Re: subset of range defined by start date and date date in dedicated cells

    I received two different and effective solutions. Thank you! I have some studying to do to understand these, but they both work beautifully.

+ 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. Does a task start or finish date fall within 3 month range of a specific date
    By jamesmcgallan in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-19-2015, 07:46 AM
  2. Replies: 1
    Last Post: 05-26-2014, 03:46 AM
  3. Replies: 1
    Last Post: 10-02-2012, 02:42 PM
  4. Replies: 0
    Last Post: 07-17-2012, 10:11 AM
  5. Set Date Range within a defined range, not to start before first date defined
    By Chicago_girl1 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-06-2012, 01:31 PM
  6. Replies: 0
    Last Post: 03-30-2012, 01:56 PM
  7. Replies: 2
    Last Post: 01-04-2012, 09:15 AM

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