Lowest number above 0
 

If I have a list of numbers in colum "A".

A1: 10
A2: 20
A3: 00
A4: 15
A5: 50
A6: 25
I can get my answer by using this formula
=SMALL(A1:A5,COUNTIF(A1:A5,0)+1)

However, if I want the smallest number between certain cells like A1, A3, A4
which would give me an answer of 10, is that posible??

Thanks in advance

Lowest number above 0
 

There is a trick to using SMALL with disjoint ranges.

Define a Named Range, say "sampl", of A1, A3, A4.
If there is at most one zero in the range, then:

=IF(SMALL(sampl,1)=0,SMALL(sampl,2),SMALL(sampl,1) )

will return the minimum not including that zero.
--
Gary''s Student - gsnu200718


"Abnerz" wrote:

If I have a list of numbers in colum "A".

A1: 10
A2: 20
A3: 00
A4: 15
A5: 50
A6: 25
I can get my answer by using this formula
=SMALL(A1:A5,COUNTIF(A1:A5,0)+1)

However, if I want the smallest number between certain cells like A1, A3, A4
which would give me an answer of 10, is that posible??

Thanks in advance

Lowest number above 0
 

You could try:
=SMALL((A1,A3,A4,A5,A7),INDEX(FREQUENCY((A1,A3,A4, A5,A7),0),1)+1)


"Abnerz" wrote:

If I have a list of numbers in colum "A".

A1: 10
A2: 20
A3: 00
A4: 15
A5: 50
A6: 25
I can get my answer by using this formula
=SMALL(A1:A5,COUNTIF(A1:A5,0)+1)

However, if I want the smallest number between certain cells like A1, A3, A4
which would give me an answer of 10, is that posible??

Thanks in advance

Lowest number above 0
 

Thank you both, wither way gave me the answer that I needed.

Regards

"JMB" wrote:

You could try:
=SMALL((A1,A3,A4,A5,A7),INDEX(FREQUENCY((A1,A3,A4, A5,A7),0),1)+1)


"Abnerz" wrote:

If I have a list of numbers in colum "A".

A1: 10
A2: 20
A3: 00
A4: 15
A5: 50
A6: 25
I can get my answer by using this formula
=SMALL(A1:A5,COUNTIF(A1:A5,0)+1)

However, if I want the smallest number between certain cells like A1, A3, A4
which would give me an answer of 10, is that posible??

Thanks in advance