Find Smallest Non-Zero Value in Column

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
AB
12
20
30
40
50
60
70
8
9#DIV/0!
10#NAME?
11
120
13
14dog
155
165
17
18
19
200
215
226
237
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
 
Upvote 0
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.
 
Upvote 0
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! :biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top