Results 1 to 2 of 2

Thread: moving minumum

  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Thanked 0 Times in 0 Posts

    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


  2. #2
    WS Lounge VIP sdckapr's Avatar
    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?


Posting Permissions

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