# Max & Min unknown malfunction with multiple criteria

1. ## Max & Min unknown malfunction with multiple criteria

I have a spreadsheet (attached) with historic weather data for a certain location.

Column A (Weather Day)
1-Jan
2-Jan
3-Jan
...
...
31-Dec

Column B (Record High)
28.4
39.2
37.4
...
...
39.2

Column C (Record Low)
-36.4
-38.2
-32.8
...
...
-36.4

I have a user defined/input "Entry Date" & "Exit Date"

The code to return the max is as follows: {=TEXT(MAX((Weather_Day>=Entry_Date)*(Weather_Day<=Exit_Date)*(Temp_Record_High)), "0.0") & " ᵒF"}
This code works perfectly fine for all input dates and returns the Record High between any Entry/Exit day.

The code to return the min is as follows: {=TEXT(MIN((Weather_Day>=Entry_Date)*(Weather_Day<=Exit_Date)*(Temp_Record_High)), "0.0") & " ᵒF"}
It's identical except MAX is now MIN. However, the returned MIN doesn't always return the correct value. It is either correct or returns 0.0.

Examples would be:

Entry Date: 4-20
Exit Date: 6-25
Both values return correctly

Entry Date: 4-21
Exit Date: 6-25
Record High returns correctly, Record Low = 0.0

Entry Date: 4-21
Exit Date: 11-7
Both values return correctly. However, with 4-21 as the Entry, 11-7 is the first Exit date to return a correct MIN value.

Thoughts or suggestions?

2. ## Re: Max & Min unknown malfunction with multiple criteria

This is a math expression that is done first.
(Weather_Day>=Entry_Date)*(Weather_Day<=Exit_Date)*(Temp_Record_Low)
Taking True/False results in the first 2 arrays.
When True/False is multliplied against a numeric value, True=1 False=0
So in rows where either of the 2 criteria are NOT met, then a 0 is included in that math.
Making the result of that expression 0.
And the math is still completed on every single row, even if the criteria are false.
So 0's are included in the array being passed to MIN.
And 0 is a valid number for the MIN function to return.

So in a date range of 4/12 to 6/25, there were no temperature values below 0
So 0 becomes the smallest number in the array.

Try it like this, so the math isn't actually done on every single row, only the rows where the criteria is met.
=TEXT(MIN(IF(Weather_Day>=Entry_Date,IF(Weather_Day<=Exit_Date,Temp_Record_Low,""),"")), "0.0") & " ᵒF"

3. ## Re: Max & Min unknown malfunction with multiple criteria

Beautiful. Thank you sir!

4. ## Re: Max & Min unknown malfunction with multiple criteria

You're welcome.

I made a minor adjustment to the formula, not sure if it makes a difference or not.
ReCopy the formula.

5. ## Re: Max & Min unknown malfunction with multiple criteria

I copied what you pasted and it functions great. Thanks again.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1