Find minimum (ignoring blank cell or zero values) with Multiple Criteria

[Joeywei]

Hi,

I have managed to get the array to work using

{=MIN(IF((A2:A12=E2)*(B2:B12=F2),C2:C12))}

However, it is taking the "blank cells" as "zero" as the minimum values. How do I write it so that it will ignore the blank cell? And how to write it if trying to get it to ignore "zero" values?

If someone can help me... I really appreciate it.

Thank you in advance.

Joey

[vlady]

try it like this

Please Login or Register  to view this content.

[zbor]

or

Please Login or Register  to view this content.

[Joeywei]

Ah... I see how you included the Nested IF to ignore blank cells. Thank you vlady!

Following your 'Nested IF' idea, I've further changed the equation to also ignore "zero values". I thought I should post the formula here for future reference if anyone else looking for such answer.

Please Login or Register  to view this content.

[daddylonglegs]

If you want to ignore blanks and zeroes then a single test for that is sufficient, i.e.

=MIN(IF((A$2:A$12=E2)*(B$2:B$12=F2)*(C$2:C$12<>0),C$2:C$12))

[Joeywei]

Thank you all for quick response.

How do I get the equation to return a "No Data" instead of "zero" when Column C has only blank for the selected criteria?

Thanks again!

[Joeywei]

Thanks zbor

[vlady]

with any of the formula's given above you can include an if statement

=if(theformula=0,"No Data",theformula)

note: take the shortest formula by DDL.

regards,
vladimir

[Joeywei]

:D... Thanks so much vladimir - Works perfectly!

Cheers
Joey

[daddylonglegs]

MIN always returns zero if no criteria are met, if you switch to SMALL you can get #NUM! error instead, i.e.

=SMALL(IF((A$2:A$12=E2)*(B$2:B$12=F2)*(C$2:C$12<>0),C$2:C$12),1)

....and take that one step further by adding IFERROR to convert to "No data", i.e.

=IFERROR(SMALL(IF((A$2:A$12=E2)*(B$2:B$12=F2)*(C$2:C$12<>0),C$2:C$12),1),"No data")

[Joeywei]

Ah... I have not known about SMALL and the IFERROR commands. This is exactly what I need to run my calculations! Thank you daddylonglegs

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

[Joeywei]

Thank you arlu. I'm learning how to navigate this site!