Lowest number
 

I am struggling to find a formula that will allow me to find the lowest
number within a range that doesn't include either blanks or 0's in the
answer. Does anyone know of a simple solution?

Thanks
--
Yorkie

Lowest number
 

Try this array* formula:

=MIN(IF(A1:A9<0,A1:A9))

assuming your range of numbers occupies A1 to A9 - adjust as necessary.

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) instead of
just ENTER. If you do this correctly then Excel will add curly braces {
} around the formula when viewed in the formula bar - you must not type
these yourself.

Hope this helps.

Pete

The Countryman wrote:

I am struggling to find a formula that will allow me to find the lowest
number within a range that doesn't include either blanks or 0's in the
answer. Does anyone know of a simple solution?

Thanks
--
Yorkie

Lowest number
 

Pete_UK wrote...
Try this array* formula:

=MIN(IF(A1:A9<0,A1:A9))
....

While this array formula may be more efficient, this can be done
without array formulas.

=LARGE(A1:A9,COUNTIF(A1:A9,"0"))

Lowest number
 

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


"The Countryman" wrote:

I am struggling to find a formula that will allow me to find the lowest
number within a range that doesn't include either blanks or 0's in the
answer. Does anyone know of a simple solution?

Thanks
--
Yorkie

Lowest number
 

Won't negative numbers cause a problem with this and Teethless Mama's formula?


"Harlan Grove" wrote:

Pete_UK wrote...
Try this array* formula:

=MIN(IF(A1:A9<0,A1:A9))
....

While this array formula may be more efficient, this can be done
without array formulas.

=LARGE(A1:A9,COUNTIF(A1:A9,"0"))

Lowest number
 

JMB wrote...
Won't negative numbers cause a problem with this and Teethless Mama's formula?
....

If both positive and negative numbers are possible, zeros shouldn't be
excluded, and MAX and MIN already skip blank cells.