# Thread: max/min in array formula (2002)

1. ## 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. ## 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).

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

and

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

3. ## 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
•