+ Reply to Thread
Results 1 to 5 of 5

Max & Min unknown malfunction with multiple criteria

  1. #1
    Registered User
    Join Date
    02-14-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    28

    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. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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"
    Last edited by Jonmo1; 02-14-2014 at 05:58 PM.

  3. #3
    Registered User
    Join Date
    02-14-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Max & Min unknown malfunction with multiple criteria

    Beautiful. Thank you sir!

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #5
    Registered User
    Join Date
    02-14-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Max & Min unknown malfunction with multiple criteria

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA: 2 open workbooks; 1 unknown; activating the unknown one
    By Janis Rainis in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-05-2014, 08:46 AM
  2. Multiple Mode Formula Malfunction. Please Help?
    By pfallonj in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-13-2012, 07:58 PM
  3. Replies: 0
    Last Post: 04-18-2012, 06:51 AM
  4. Filter by unknown criteria problem
    By thornton in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2011, 12:56 PM
  5. Display 'UNKNOWN' in a cell if the criteria doesn't match
    By famico78 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-27-2008, 09:55 AM

Bookmarks

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