1. ## moving minumum

I have 60 years worth of daily data and I would like to find the minimum value (column D) for a period starting 30 days less than current date to 30 days greater than current date. I could select the current date (midpoint e.g. cell C120) and then have an =min(D90:150) and copy the rows down.

Out of curiousity, is there an easier/better way? I though of array formulas, but the computation time could be large

Thanks

2. An array could take a long time, but is generic. If your data is sorted by date you could use a MATCH to find row for the 30 days before and taking the next 60 days after and then use indirect or even an offset to get the minimum. [I don't understand how you would have any values after the current date....]

For example the formula:
=MIN(OFFSET(C1,MATCH(TODAY()-30,C:C,0)-1,1,60,1))

will give the minimum value in column D of the range of 60 values starting with the date 30 days before today and getting the next 60 days.

If the setup is different, could you elaborate on it?

Steve

#### Posting Permissions

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