# 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?

• Re: Minimum Temperature Between Two Dates

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

• Re: Minimum Temperature Between Two Dates

Thanks Parsnip.

That works.