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

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

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

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