Min formula excluding any 0's or empty cells

[melobster]

I have tried a number of different ones from my search of this forum but I'm getting an error. here is a sample of my spreadsheet.

I want it to give me the lowest number, I don't want it to look at the 0's or if the cell is blank.
Here is my formula and my error:
=SMALL(N196:N203,COUNTIF($N$196:$N$203,0)+1)

my error is #num!

I've also tried others including using the MIN function but either 0 is entered or the error above.

<TABLE style="WIDTH: 54pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=72 border=0 x:str><COLGROUP><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=72 height=17>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17>4</TD></TR></TBODY></TABLE>

thank you in advance!

 

[lenze]

=MIN(IF($N$196:$N$203>0,$N$196:$N$203))

Enter with CTRL+SHIFT+ENTER
lenze

 

[melobster]

Thank you for such a quick reply. I am getting a value of 0.00

 

[lenze]

Did you enter using CTRL+SHIFT+ENTER? If done correctly, the formula bar will display

{=MIN(IF($N$196:$N$203>0,$N$196:$N$203))}

lenze

 

[melobster]

I tried again in a different section of my spreadsheet and I'm still getting 0 (zero) as my returned value
Here is what is in my bottom cell that shows 0:
{=MIN(IF($N$334:$N$341>0,$N$334:$N$341))}

Here are the cells (the bottom cell is the cell with the formula): Some cells have numbers including zero or is blank. I want to ignore the zeros and blank cells in my formula. Thank you!!
<TABLE style="WIDTH: 54pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=72 border=0 x:str><COLGROUP><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_1884194 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=72 height=17>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=14> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>4</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 9.95pt; BACKGROUND-COLOR: transparent" height=13> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num x:fmla="=MIN(IF($A$1:$A$8>0,$A$1:$A$8))" x:arrayrange="A10">0


</TD></TR></TBODY></TABLE>

 

[Dave Patton]

Are the "numbers" actually text?
a) =ISNUMBER(A2)
b) Check your data for extraneous hidden characters.


If the area was preformatted as text or the numbers were imported as text, you can try =MIN(IF($A$1:A$10+0>0,$A$1:A$10+0))

Edit the ranges as required and Array Enter the formula,

 

[lenze]

I agree with Dave. From your example, it appears your numbers are text. You can change them to numbers by selecting the column and Choosing Data>Text to Columns. When the wizard appears, click Finish!

lenze

 

[xlHammer]

This formula;

=LARGE(A1:A10,COUNTIF(A1:A10,">0"))

will give you the smallest number that is greater than zero. Seems to work ok even if the numbers are text. Obviously if your smallest number is less than zero it won't work.

Any help?

 

[melobster]

I think I figured out my what's causing the error but now I don't know how to fix it.
Each number that you see above in each cell is a result of a formula.
ie =IF(J334="x","3","0") The number three in the above first cell is there because I had it look at cell J334 and if it has an 'x' in it then the person would receive a 3 rating and the number 3 is entered in cell n334. If not then enter 0.
the next cell down is =IF(I334="x","4","0") that's why there is a 4 in that cell. etc.
When I was looking for help with my formula in the last cell down the column that is looking at the cells above it for the lowest number (not a 0 or blank) and I assumed that since they were numbers your formulas would work but now based on the last two comments I'm wrong.
Its actually looking at a result of a formula right?

How do I adjust the formula now? Am I making sense?

 

[Dave Patton]

a) you can change your formula from =IF(J334="x","3","0") to =IF(J334="x",3,0) N.B. the quotes like "3" yield text.

b) make a copy of your spreadsheet
in the data rows, convert the formulas to values
- copy the range (Ctrl+C)
- Edit Paste Special Values (this ELIMINATES the formulas)


d) change the data, convert the text to actual numbers.
Alternatives include:
- Data Text to Columns see lenze's suggestion or

- select a blank cell, then copy it (Ctrl+C)
select the range of data
Edit PasteSpecial Add

Then try one of the formulas like =SMALL(A1:A20,COUNTIF(A1:A20,0)+1) or
array enter =MIN(IF(A1:A20>0,A1:A20))