Find lowest value in range excluding 0

[karini4]

I have been trying to enter a formula to find the lowest value in a range, but I want to exclude 0's. I haven't been able to figure it out. Any suggestions?

 

[Peter_SSs]

hi, here's a non-array formula for the hell of it (besides, online spreadsheets dont supports arrays, in case you wanna upload):

if there are no negatives:
=SMALL(A1:A30,COUNTIF(A1:A30,0)+1)

if negatives are possible:
=CHOOSE((MIN(A1:A30)>=0)+1,MIN(A1:A30),SMALL(A1:A30,COUNTIF(A1:A30,0)+1))

If this is answering the most recent question (by MediChick) then I think you may have missed that the formula needs to look at multiple sheets.

If we were jusy looking at A1:A30 on a single sheet and negatives are allowed

a) your suggested formula does not work for me.

b) couldn't you just use this modification of your first formula?

=SMALL(A1:A30,COUNTIF(A1:A30,"<=0")+1)

 

[sulakvea]

Peter, i missed that the thread contained more than 1 pages, so what i saw (and what i added to) was a couple array suggestions to the original Q. i.e. i didnt see the discussion about multiple sheets, etc. happens, it's been a long week

plus, both formulas work for me (granted they just answer the original question about non-zero minimums).

and, =SMALL(A1:A30,COUNTIF(A1:A30,"<=0")+1) actually didnt work for me when there are negatives in the range.

HTH

 

[Peter_SSs]

plus, both formulas work for me (granted they just answer the original question about non-zero minimums).

and, =SMALL(A1:A30,COUNTIF(A1:A30,"<=0")+1) actually didnt work for me when there are negatives in the range.

Yep, I mis-interpreted the question to mean "lowest value above 0". Also happens.

 

[sulakvea]

 

[Sneakrz]

Similar problem.. hope someone can help...
Looking for the lowest value in a column. The data will not contain negative numbers...
This may be a bit elementary but here goes..

When I copy and paste this array formula into my spreadsheet:
=SMALL(A1:A30,COUNTIF(A1:A30,"<=0")+1)
I get an answer of 5 so it works - but with the wrong data. The data it is working with (A1:A30) is imported from Access DB ranging from Column A to Column AH (ie it is blue)

When I change both instances of A1:A30 to::
=SMALL(GF3:GF11,COUNTIF(GF3:GF11,0)+1)
I get #NUM!

Why??

I suspect it is the data in GF3:GF11 which contain formulas similar to:
=IF(B3=GA$4,R3,) and return a number like 432.2
(B3 and R3 is data from the same Access DB and GA$4 is what I am looking for (the number 2 in this instance).
I have formatted Columns GF, GA, B, R as numbers.

I have also tried =DMin, =Small with similar failures...

Any ideas what I have done wrong?
Thanks
JL

 

[Aladin Akyurek]

Similar problem.. hope someone can help...
Looking for the lowest value in a column. The data will not contain negative numbers...
This may be a bit elementary but here goes..

When I copy and paste this array formula into my spreadsheet:
=SMALL(A1:A30,COUNTIF(A1:A30,"<=0")+1)
I get an answer of 5 so it works - but with the wrong data. The data it is working with (A1:A30) is imported from Access DB ranging from Column A to Column AH (ie it is blue)

When I change both instances of A1:A30 to::
=SMALL(GF3:GF11,COUNTIF(GF3:GF11,0)+1)
I get #NUM!

Why??

I suspect it is the data in GF3:GF11 which contain formulas similar to:
=IF(B3=GA$4,R3,) and return a number like 432.2
(B3 and R3 is data from the same Access DB and GA$4 is what I am looking for (the number 2 in this instance).
I have formatted Columns GF, GA, B, R as numbers.

I have also tried =DMin, =Small with similar failures...

Any ideas what I have done wrong?
Thanks
JL

The range of interest is GF3:GF11.

The range is numeric and consists of non-negative numbers.

Goal: Find the non-zero minimum number.

=SMALL(GF3:GF11,COUNTIF(GF3:GF11,0)+1)

=SMALL(GF3:GF11,INDEX(FREQUENCY(GF3:GF11,0),1)+1)

{=MIN(IF(GF3:GF11,GF3:GF11))}

All would yield the intended outcome.

What does yield the following yield

=MIN(IF(ISNUMBER(GF3:GF11+0),IF((GF3:GF11+0)>0,GF3:GF11+0)))

which must be confirmed with control+shift+enter, not just enter?

 

[Sneakrz]

Thank you for the help on this..
Your understanding is correct..

=SMALL(GF3:GF11,COUNTIF(GF3:GF11,0)+1)
#NUM!

=SMALL(GF3:GF11,INDEX(FREQUENCY(GF3:GF11,0),1)+1)
#NUM!

{=MIN(IF(GF3:GF11,GF3:GF11))}
#VALUE!

=MIN(IF(ISNUMBER(GF3:GF11+0),IF((GF3:GF11+0)>0,GF3:GF11+0)))
450.8 (which is the not the lowest number)
Here are the current values of GF3:GF11
<table border="0" cellpadding="0" cellspacing="0" width="64"><col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" align="right" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">448.4</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">449.7</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">450.8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">451.2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">452.4</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">453.6</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">454.5</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">457.2</td> </tr> </tbody></table>
But I think we are on to something here...
The larger numbers will automatically left justify
The 0 automatically right justifies..
if that is of any help..

Thanks!!
JL

 

[Aladin Akyurek]

Thank you for the help on this..
Your understanding is correct..

=SMALL(GF3:GF11,COUNTIF(GF3:GF11,0)+1)
#NUM!

=SMALL(GF3:GF11,INDEX(FREQUENCY(GF3:GF11,0),1)+1)
#NUM!

{=MIN(IF(GF3:GF11,GF3:GF11))}
#VALUE!

=MIN(IF(ISNUMBER(GF3:GF11+0),IF((GF3:GF11+0)>0,GF3:GF11+0)))
450.8 (which is the not the lowest number)
Here are the current values of GF3:GF11
<TABLE border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 48pt; HEIGHT: 15pt" height=20 width=64 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>448.4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>449.7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>450.8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>451.2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>452.4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>453.6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>454.5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>457.2</TD></TR></TBODY></TABLE>
But I think we are on to something here...
The larger numbers will automatically left justify
The 0 automatically right justifies..
if that is of any help..

Thanks!!
JL

You have text numbers in the range, not true numbers. Try to convert them into true numbers. One way would be:

Copy an empty/unused cell.
Select GF3:GF11.
Activate Paste Special | Add

 

[Sneakrz]

Each column is formatted as number (with one decimal point)... if that matters.

Each day 2-4 new records will be added... and while the MIN value will not change often, it will change on occasion and I planned to make other formulas and values rely on this calculation...

Is there an easier way? I would prefer not to use macros (I have enough of them already) and I dont know much about VBA but I can (and would rather) learn if that is what is needed..

I would be open to any other ideas..
JL

 

[Sneakrz]

I have found a work around..

My problem is that my formulas were in fact delivering numbers as text. My formula was:
=IF(B6=GA$4,R6,)

What I found is that by changing the formula to:
=IF(B6=GA$4,R6,)*1
viola,... I have numbers

Anyone see any drawbacks with this approach?
Thanks to everyone esp Aladin for your help!!