CODEWITHSUNDEEP
excelexcel-formula
Hiddenllyy
Hiddenllyy

Reputation: 179

EXCEL Formula_use AGGREGATE function as MIN but meet error

I'm trying to use AGGREGATE function as MIN to choose the largest and the smallest number in one column. Because AGGREGATE can also ignore the DIV/0 during this progress.

Apple1 | weight | 1.2
Apple1 | height | 0.5
Apple1 | price  | 1
Apple2 | weight | DIV/0
Apple2 | height | 1.1
Apple2 | price  | 1
Apple3 | weight | 1.1
Apple3 | height | 0.8
Apple3 | price  | 1

And here is my code:

=AGGREGATE(15;3;(B1:B9="weight")*(C1:C9);1)

But when I try to use MIN to choose the smallest in the positive results or use MAX to choose the biggest in the negative results, the search result is always 0 but not the "correct" one. In the example above, it should be 1.1, but it gives me 0.

Upvotes: 3

Views: 81

Answers (1)

XOR LX
XOR LX

Reputation: 7742

With AGGREGATE, only a function parameter of 14 or greater is compatible with an array parameter which is not a worksheet range (or else some construction which resolves to a worksheet range).

However, since the combination of function parameter 15 (SMALL) with k parameter 1 is equivalent to finding the minimum, you should employ this version, viz:

=AGGREGATE(15;3;C1:C9/(B1:B9="weight");1)

Regards

Upvotes: 2

Related Questions