lowest figure greater than 1

K

Kendo

I need a formula to show the lowest figure, but not 0 in an excel spreadsheet.

Can anyone help???!!
 
S

Sheeloo

Do you want the minimum number in a range excluding zero?

If yes then copy this in a cell
=MIN(IF(A1:A100=0,"",A1:A100))
and press CTRL-SHIFT-ENTER

Adjust 100 to the last row in your data set.
 
D

David Biddulph

For the OP's benefit, if you just wanted to exclude zero (and not exclude
negative values), Max's formula would become
=MIN(IF(A1:A10<>0,A1:A10))
whereas if you wanted only to use numbers greater than 1 (as in your subject
line), it would become
=MIN(IF(A1:A10>1,A1:A10))
 
R

RagDyeR

To exclude 0's and negatives, you could try:

=Small(A1:A50,1+Countif(A1:A50,0))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



I need a formula to show the lowest figure, but not 0 in an excel
spreadsheet.

Can anyone help???!!
 
H

Harlan Grove

Kendo said:
I need a formula to show the lowest figure, but not 0 in an excel spreadsheet.
Click to expand...

So smallest positive? That'd mean > 0, but your subject line says > 1.

This can be done in general without array formulas as

=SMALL(range,COUNTIF(range,"<="&x)+1)

which returns the smallest value in range greater than x. That said,
the array formula

=MIN(IF(range>x,range))

is more efficient - faster recalc, uses fewer resources.
 
Top