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