Results 1 to 2 of 2
Thread: moving minumum
2011-08-15, 14:44 #1
- Join Date
- May 2002
- Thanked 0 Times in 0 Posts
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
2011-08-15, 18:22 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 342 Times in 335 Posts
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:
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?