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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

dwgnome

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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,108,843
Messages
5,525,179
Members
409,629
Latest member
McGuilliam

This Week's Hot Topics

Top