Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    max/min in array formula (2002)

    I'm trying to find the max and min from a set of numbers. The numbers are found by looking at a subset of a large group of numbers based on one criteria.
    I'm using an array formula to find the result. The formula looks something like: MAX(1*(DATA!AA2:AA1112)*(B12=DATA!AF2:AF1112)) [array formula]
    When I use MAX, I get the result I expect. When I make a copy of this formula (yes, I'm using absolute values also, just didn't put the $-signs above), and use MIN, it doesn't work. I get a 0 rather than the non-zero real MIN value.

    Is it that MAX and MIN don't work in array formulas and I just got lucky with MAX? Or is there something someone can see that I'm overlooking without trying to attach the sample file?

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: max/min in array formula (2002)

    If a cell in AF12:AF1112 is *not* equal to B12, the value of B12=DATA!AF2:AF1112 is FALSE, this equals 0. So you'll get zeros in your array, and hence the minimum is 0 (unless you had negative values in column AA).

    Use these array formulas instead:

    =MIN(IF(DATA!AF2:AF1112=B12,DATA!AA2:AA1112))

    and

    =MAX(IF(DATA!AF2:AF1112=B12,DATA!AA2:AA1112))

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: max/min in array formula (2002)

    DUH. Thanks, Hans...

Posting Permissions

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