Finding Minimum Value in series, excluding zero values

[cpretzinger]

I am trying to find the smallest value in a series, but exclude the
cells that have zero values.

For Example:


A B C D E F G

2 1.1 4 0 0 0 0

I want the return value to be 1.1 (the smallest in the row excluding
0)

Is this possible?

 

[Guest]

Try this ARRAY FORMULA:
=MIN(IF(A1:G1<>0,A1:G1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Non array formula

=SMALL(A2:G2,COUNTIF(A2:G2,0)+1)

 

[Guest]

I was on the fence about that formula...negative values cause incorrect
returned values. Hence, the array formula I posted.

To exclude zeroes AND negative numbers, I think I would go with something
like this non-array, instead:
=SMALL(A2:G2,COUNTIF(A2:G2,"<=0")+1)

That one returns the MIN Positive value.

Otherwise, if the need is for MIN NON-ZERO value (which may be pos or neg,
then maybe this?
=MIN(INDEX(A2:G2+(A2:G2=0)*10^99,0))


***********
Regards,
Ron

XL2002, WinXP

 

[Harlan Grove]

I was on the fence about that formula...negative values cause incorrect
returned values. Hence, the array formula I posted.

....

If there were negative values, MIN would return the negative value
with the greatest absolute value. If negative and positive values are
possible, continuity means zero values should also be excluded.

Most questions like this arise when there are only positive and zero
values. Anyway, if you want to be safe, LARGE is just as [in]efficient
as SMALL, so use either

=LARGE(rng,COUNTIF(rng,">0"))

or the array formula

=MIN(IF(rng>0,rng))

 

[Guest]

Thanks, Harlan
I like the LARGE option, even though I keep forgetting about that
one...and...as I recall, you've reminded me of it before. : \

The only reason I included the min non-zero option was in case that's what
was actually needed...but not asked for.

***********
Best Regards,
Ron

XL2002, WinXP

I was on the fence about that formula...negative values cause incorrect
returned values. Hence, the array formula I posted.

....

If there were negative values, MIN would return the negative value
with the greatest absolute value. If negative and positive values are
possible, continuity means zero values should also be excluded.

Most questions like this arise when there are only positive and zero
values. Anyway, if you want to be safe, LARGE is just as [in]efficient
as SMALL, so use either

=LARGE(rng,COUNTIF(rng,">0"))

or the array formula

=MIN(IF(rng>0,rng))