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

1. ## 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!

2. ## 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. ## 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)

4. ## 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.

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

#### 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