Find Smallest Non-Zero Value in Column

[dwgnome]

Hi,

Having a difficult time finding the difference between the maximum number and the smallest non-zero value that ignores text and any error messages. What I need to do is subtract the Maximum value in the column from the smallest non zero value. In the case below, it should take 7 minus 5 and answer is 2.

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=80 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=80><TBODY><TR style="HEIGHT: 12.75pt" height=21><TD class=xl22 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #333399 1pt solid; BORDER-LEFT: #333399 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ccffcc" width=80 height=21>Data</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21></TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>0.0</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>0.0</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>0.0</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>0.0</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>0.0</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>0.0</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21></TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:fmla="=5/0" x:err="#DIV/0!">#DIV/0!</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:err="#NAME?">#NAME?</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21></TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>0.0</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21></TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21>dog</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>5.0</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>5.0</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21></TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21></TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21></TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>0.0</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>5.0</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>6.0</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>7.0</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl24 style="BORDER-RIGHT: #333399 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #333399 1pt solid; BORDER-BOTTOM: #333399 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=23></TD></TR></TBODY></TABLE>

I have tried the following formula:

=IF(SUM(F6:F29) < 1,0,MAX(F6:F30)-SMALL(F6:F30,COUNTIF(F6:F30,0)+0)) resulting in #DIV/0!.

I even tried to isolate the COUNTIF portion by using:

=SUMPRODUCT((--(ISTEXT($F$6:$F$29))+(ISERROR($F$6:$F$29))+(ISBLANK($F$6:$F$29))))+COUNTIF(F6:F29,"<=0")

with some success. The SUMPRODUCT formula resulted in 19, which I think is correct, but when placed inside the SMALL function, it still shows up as #DIV/0!. Now I normally would not have these errors, but I am using them as surrogates for any error message type.

Can someone lead me in the right direction on how to find the MAX/MIN avoiding text, zeros and blanks.

 

[MrKowz]

Try this:

=MAX(IF(NOT(ISERROR(A1:A23)),A1:A23,""))-MIN(IF(NOT(ISERROR(A1:A23)),IF(A1:A23<>0,A1:A23,""))

Confirm entry with CTRL+SHIFT+ENTER to store it as an array formula. you will know you enter it properly when brackets {} appear around the formula.

Excel Workbook
	A	B
1		2
2	0
3	0
4	0
5	0
6	0
7	0
8
9	#DIV/0!
10	#NAME?
11
12	0
13
14	dog
15	5
16	5
17
18
19
20	0
21	5
22	6
23	7
Sheet1

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

 

[dwgnome]

Thank you MrKowz it seems to work perfectly.

I tried following the formula using Excel's Evaluate Formula, but still cannot follow what the formula is doing. Could you or someone explain?

Also can a non-CSE version be developed?

Thanks.

 

[MrKowz]

As a simple formula, I don't think you can get a non-CSE alternative.

In essence, what the formula does is:

MAX(IF(NOT(ISERROR(A1:A23)),A1:A23,""))

Let's isolate the NOT(ISERROR(A1:A23)). This will look at all values in the range and return a TRUE statement for any cell that is NOT an ERROR.

When added to an IF, IF(NOT(ISERROR(A1:A23)),A1:A23,""), it will use the condition (which evaluates as an array like TRUE,TRUE,TRUE,FALSE,FALSE,TRUE etc), and then returns the corresponding values.

So if you had a range where the values were 1,5,0,#NAME?,#DIV/0,1 then the condition will return TRUE,TRUE,TRUE,FALSE,FALSE,TRUE, and then the IF statement will return you the values 1,5,0,1.

The MIN part of the formula works in a similar way. It first returns an array of numbers that are NOT errors, then it does another check on that array it returns to weed out the non-zero values.

Hope that explanation helps!

 

[dwgnome]

Yes, indeed.

Thank you!