+ Reply to Thread
Results 1 to 3 of 3

MAX with criteria fails when using negative numbers

  1. #1
    Forum Contributor
    Join Date
    05-24-2006
    Location
    Los Angeles
    MS-Off Ver
    2019
    Posts
    326

    MAX with criteria fails when using negative numbers

    Hi,

    I'm using MAX with SUMPRODUCT to find the maximum value in a range based on a corresponding range of TRUE/FALSE criteria. The problem is when the values are all negatives, the formula result equals zero when it should equal the smallest negative value.

    In column A, I have -1, -2 and -3

    In column B, I have TRUE, TRUE and FALSE.

    The formula is =SUMPRODUCT(MAX((B1:B3=TRUE)*(A1:A3)))

    Now, I could use the LARGE function, but why does MAX evaluate to zero when it should evaluate to -1???

    Thanks,

    Lawrence
    Last edited by skysurfer; 06-25-2013 at 03:24 PM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: MAX with criteria fails when using negative numbers

    Because you are multiplying FALSE * -3 = 0 so you get zero as the result, use an "array formula" like this:

    =MAX(IF(B1:B3=TRUE,A1:A3))

    confirmed with CTRL:+SHIFT+ENTER
    Audere est facere

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: MAX with criteria fails when using negative numbers

    Try this, comfirmed with ctrl+shift+enter

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    For above solution, use Evaluate Function and you'll see the problem

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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