Min Function that ignores values of Zero

E

edvolchok

How do I write a function that will find the lowest value greater than
zero in a range of cells? This data set will never have negative
numbers.

Thanks,

Edward Volchok
 
J

JE McGimpsey

How do I write a function that will find the lowest value greater than
zero in a range of cells? This data set will never have negative
numbers.

One way:

Array-entered (CMD-RETURN):

=AVERAGE(IF(A1:A10>0,A1:A10))

Alternatively:

=SUMIF(A1:A10,">0")/COUNTIF(A1:A10,">0")
 
E

edvolchok

One way:

Array-entered (CMD-RETURN):

=AVERAGE(IF(A1:A10>0,A1:A10))

Alternatively:

=SUMIF(A1:A10,">0")/COUNTIF(A1:A10,">0")

Thank you for responding so quickly. Unfortunately the two formulas
you suggest, while great ways of finding the arithmetic mean, do not
answer my question. What I'm looking for is a way of finding the
lowest non-zero value. This can be done by modifying your array-
entered formula to read "{=MIN(IF(A10:A10>0,A1:A10)).}" I'd be
delighted to find a formula that would not require an array. Any
ideas?

Thanks again.

Ed Volchok
 
J

JE McGimpsey

Thank you for responding so quickly. Unfortunately the two formulas
you suggest, while great ways of finding the arithmetic mean, do not
answer my question. What I'm looking for is a way of finding the
lowest non-zero value. This can be done by modifying your array-
entered formula to read "{=MIN(IF(A10:A10>0,A1:A10)).}" I'd be
delighted to find a formula that would not require an array. Any
ideas?

Sorry - got sidetracked on a different question. The array-entered MIN
formula is the best way to do this. There's no other non-array function
that will work.

You could write a User Defined Function to calculate it, but it would be
relatively slow, and would become useless in XL08.
 
C

CyberTaz

May not be elegant, but here's _one_ other approach which may work for
you... And it doesn't require an array:

Assuming the values are in cells A2:A10 with a caption in A1;

Caption any other cell (let's say H7) with the same caption and in the cell
beneath it (H8) enter >0 - those two cells serve as a "Criteria Range", then
write a DMIN fx like this:

=DMIN(A1:A10,A1,H7:H8)

You can also change or delete the ">0" in the criteria range (H8 in this
case) any time you wish & the fx will update accordingly.

HTH |:>)
Bob Jones
[MVP] Office:Mac
 
E

edvolchok

May not be elegant, but here's _one_ other approach which may work for
you... And it doesn't require an array:

Assuming the values are in cells A2:A10 with a caption in A1;

Caption any other cell (let's say H7) with the same caption and in the cell
beneath it (H8) enter >0 - those two cells serve as a "Criteria Range", then
write a DMIN fx like this:

=DMIN(A1:A10,A1,H7:H8)

You can also change or delete the ">0" in the criteria range (H8 in this
case) any time you wish & the fx will update accordingly.

HTH |:>)
Bob Jones
[MVP] Office:Mac

Thanks, Bob. Back when Excel was new I always used the database
functions. Somehow I forget about how versatile they are.

Ed
 
D

Domenic

What I'm looking for is a way of finding the
lowest non-zero value. This can be done by modifying your array-
entered formula to read "{=MIN(IF(A10:A10>0,A1:A10)).}" I'd be
delighted to find a formula that would not require an array. Any
ideas?

Since your data does not contain negative values, try...

=SMALL(A1:A10,COUNTIF(A1:A10,0)+1)

Hope this helps!
 
J

JE McGimpsey

Domenic said:
Since your data does not contain negative values, try...

=SMALL(A1:A10,COUNTIF(A1:A10,0)+1)

Duh!

And a variation with one less operation:

=LARGE(A1:A10,COUNTIF(A1:A10,">0"))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top