Minimum Temperature Between Two Dates

    Hi All,


    I'd like to try and use an array formula to return the minimum temperature between two dates.


    Say Column A holds a list of dates. Column B holds the temperatures for each of those dates in column A. Lets say that I want to return the minimum temperature between 10th Jan 2007 and 12th Jan 2007 with the daily temperatures being 9,10 and 11 respectively.


    I used the following array formula to return the maximum temperature between two dates and it succesfully returned 11:
    {=MAX( (A1:A10>=DATEVALUE("10/01/2007")) * (A1:A10<=DATEVALUE("12/01/2007")) * (B1:B10) )}


    However when I try and use the MIN forumla the answer I get was 0 when it should have returned 9:
    {=MIN( (A1:A10>=DATEVALUE("10/01/2007")) * (A1:A10<=DATEVALUE("12/01/2007")) * (B1:B10) )}


    Am I missing something?


    Brad

    Brad.

    Re: Minimum Temperature Between Two Dates


    bradles,


    Is it possible that there is a blank cell or labels in the range you're checking for the date? That would be a date of "0" and skew any minimum.


    Jim

    Re: Minimum Temperature Between Two Dates


    If you have dates outside of those two values, then the formula will obviously return 0 as you will be multiplying a term by 0. You need to use an IF statement to exclude:


    {=MIN( IF(A1:A10>=DATEVALUE("10/01/2007"),IF(A1:A10<=DATEVALUE("12/01/2007"),B1:B10) ))}


    Richard

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login