Lowest Value in a range

[leics27]

Can anybody tell me if there is a way to find the lowest value in a row of numbers, but only values more than zero? using the =min gives zero which I don't want.

Thanks

[davesexcel]

try =small(range,1)

[leics27]

try =small(range,1)

I've tried this but it still comes up with 0 as the result. There are mostly zeros in the range, but a few cells with numbers in them.

Would I need an =if.... as the =small will word if I tell it which lowest number (ie 4th, 5th etc) I want, but I don't want to have to do that - I just want the lowest value higher than zero.

THanks

[starguy]

Can anybody tell me if there is a way to find the lowest value in a row of numbers, but only values more than zero? using the =min gives zero which I don't want.

Thanks

try following

=IF(COUNTIF(A1:A50,"=0")>0,SMALL(A1:A50,2),SMALL(A1:A50,1))
(assuiming that A1:A50 do not contain negative values)

Regards.

[leics27]

I took your formula and edited to the correct range

=IF(COUNTIF(Q4:AA4,"=0")>0,SMALL(Q4:AA4,2),SMALL(Q4:AA4,1))

It won't do it still, but does it matter that the values in the range are formula generated? (via a lookup)

Thanks for your help

[starguy]

I took your formula and edited to the correct range

=IF(COUNTIF(Q4:AA4,"=0")>0,SMALL(Q4:AA4,2),SMALL(Q4:AA4,1))

It won't do it still, but does it matter that the values in the range are formula generated? (via a lookup)

Thanks for your help

try this

=SMALL(Q4:AA4,COUNTIF(Q4:AA4,"<=0")+1)

Regards.

[leics27]

That works, it gives the lowest value, not including zero.

However, sorry to be a right pain the a*s,

On some of the rows of data there are only zeros, so if this is the case zero is ok as the answer! Can the formula incorporate this argument too?

[Dav]

=if(countif(q4:aa4,"<=0")=count(q4:aa4),0,SMALL(Q4:AA4,COUNTIF(Q4:AA4,"<=0")+1))

Regards

Dav

[leics27]

That does the job - thanks very much!!