min and max values w/in specific date range

**Barz** · 09-30-2009, 06:59 AM

Hi,

I have a table of dates and values and would very much like to know a formula for returning the min and mean values for each column within a specified time (date) period.

Table is as follows

Date value(a) Value (b) ....
1/1/09 10 8
2/1/09 8 6
3/1/09 5 2

Thanks in advance!!

**DonkeyOte** · 09-30-2009, 07:08 AM

Are the dates always listed in order ? Where are the specified dates (criteria) stored ?

Best to post a sample file which reflects your setup and outline desired results based on sample values.

**Barz** · 09-30-2009, 07:28 AM

Dates in the table are always in order - a simplified sample sheet is attatched with the table data in sheet 1 and the output data in sheet 2.

**DonkeyOte** · 09-30-2009, 07:34 AM

Simplified versions not always ideal but based on your sample...

To avoid repetitive calcs and need for Arrays I would advise you do the following:

Sheet2!P5: =MATCH(A5,Sheet1!$A:$A,0)
copied to Q5

This gives you the row index position of each criteria date as listed on Sheet1 (we're assuming criteria dates are always listed - if not this will need to be adjusted for).

Sheet2!D5: =MIN(INDEX(Sheet1!B:B,$P5):INDEX(Sheet1!B:B,$Q5))
copied across to H5

The same can be repeated for J:N using MAX rather than MIN.