Minimum Temperature Between Two Dates

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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