CODEWITHSUNDEEP
excelmaxconditional-statements
Nat Aes
Nat Aes

Reputation: 927

Conditional Maximum

If I am looking for the maximum of a column, conditional on that number being less than 100, then I can use the following array formula:

=MAX(IF(A1:A10<100,A1:A10))

Is it possible however to make results conditional on a different corresponding column? To illustrate: if the maximum value in column A is found in A3, but B3 is more than 100 then the value in A3 will not be returned as the maximum value. The next highest value, found in cell A7, is a viable option given that it's corresponding cell (B7) is below 100.

Upvotes: 0

Views: 70

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60199

Try this array formula:

=MAX(A1:A10*(B1:B10<100))

Upvotes: 1

Related Questions