Max value with a condition

[Lukodi]

Saw some awesome answers on this forum, thought I'd give a try if you guys could help me out as well.


I saw several posts that adressed this problem, but basically all posts were string conditions and I'm not sure how I can make it work for me.


Example:


A B

1 5%

10 1,05%

39 1,23%

1 0,56%

12 1,05%

18 1,37%


I want to find the max value of column B with a condition that the value in column A must be higher than 10.


Not sure how to go about this, any of you experts know?

 

[Faseeh]

Hi Lukodi,


Welcome to the Forums!! With data like below in Cell A1:B7....

[pre]

A	B
1	5%
10	105%
39	123%
1	56%
12	105%
18	137%[/pre]
Use: 


=MAX((A2:A7>10)*(B2:B7))

Press Ctrl+Shift+Enter and format cell as percentage.


Regards,

 

[Lukodi]

Oh, it seems like that's a very simple answer and I should've known it myself.


Anyway, it works!

Thanks a lot!


EDIT:

Oh, it seems the same formula doesn't apply when I replace MAX with MIN.

Why is this and how would I solve that?

 

[Faseeh]

Hi Lukodi,


Try,

=MIN(IF((A1:A6>10),B1:B6))

Press Ctrl+Shift+Enter. Adjust ranges accordingly.


Regards,

 

[Lukodi]

Thanks a lot Faseeh!