MIN greater than zero

[bbond]

I need to find the lowest number in a row but want to ignore any cell with a zero in it for the MIN calculation. In other words 4, 2, 11, 0, 5, 0 in a row would return 2 for this answer.

I've been unable to find anything in the forum, but find it hard to believe that this hasn't come up before. If this has been answered before, just point me to the proper post.

Thanks much!

 

[Jonmo1]

Welcome to the board...


Try
=SMALL(A1:F1,1+COUNTIF(A1:F1,0))


Hope that helps.

 

[jeffreybrown]

Hello & Welcome to the Board,

Could you use...

=MIN(IF(A1:F1>0,A1:F1))

This formula requires Ctrl + Shift + Enter

 

[Aladin Akyurek]

I need to find the lowest number in a row but want to ignore any cell with a zero in it for the MIN calculation. In other words 4, 2, 11, 0, 5, 0 in a row would return 2 for this answer.

I've been unable to find anything in the forum, but find it hard to believe that this hasn't come up before. If this has been answered before, just point me to the proper post.

Thanks much!

Another option...

=SMALL(A1:F1,INDEX(FREQUENCY(A1:F1,0),2)+1)

 

[bbond]

JeffryBrown, what do you mean "requires 'Ctrl + shift + Enter'". I'm not advanced enough to know what a lot of the shortcuts you guys are using really mean.

Thanks

 

[T. Valko]

JeffryBrown, what do you mean "requires 'Ctrl + shift + Enter'". I'm not advanced enough to know what a lot of the shortcuts you guys are using really mean.

Thanks

See this...

http://www.cpearson.com/Excel/ArrayFormulas.aspx

 

[bbond]

THANKS JeffreyBrown and TValco! works like a charm

 

[jeffreybrown]

You're welcome and glad it worked for you

 

[T. Valko]

THANKS JeffreyBrown and TValco! works like a charm

Happy to pitch in!