CODEWITHSUNDEEP
excelexcel-formula
user2716568
user2716568

Reputation: 1946

Find minimum value in range after a maximum value

I have data structured as follows:

A1:A8 (10, 2, 11, 15, 17, 10, 4, 5)

I wish to find the SMALLEST value that occurs AFTER the LARGEST value. In the example above, it would be 4 or A7 and not A2.

I have tried to use the DMIN and a combination of MIN plus IF functions, but they all return the smallest value, not the smallest after the largest.

Thank you!

Upvotes: 0

Views: 81

Answers (1)

rwilson
rwilson

Reputation: 2145

Try this:

=MIN(IF((MAX(IF(A1:A8=MAX(A1:A8),ROW(A1:A8)))<ROW(A1:A8))*A1:A8>0,A1:A8))

You will need to enter this with CTRL + SHIFT + ENTER.

Upvotes: 1

Related Questions