min() function excluding 0 1

[keithvp]

Is there a function in Excel that will find the minimum value in a range while excluding any zeros as a value? Or has anyone tweaked the min function to do this?

 

[DaleWatson123321]

Keith,

The following formula will "do the trick"...

=SMALL(data,COUNTIF(data,0)+1)

...where the Range Name "data" is assigned to the range of data you are referencing.

Hope this helps. My testing confirms that it works, but feedback would be appreciated - to confirm that it also works "at your end".

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[mikewoodhouse]

I'm always a bit nervous of the SUMIF, COUNTIF etc functions performance-wise. It might be worth trying an array formula, something like:

{=MIN(IF(data>0,data,999999999))}

(type without the {} and use Control-Shift-Enter to make it into an array formula).

If the data range is fairly small it's probably not worth it (although if you aren't familiar with array formulae, they're worth getting to know).

Mike

 

[DaleWatson123321]

Hi Mike,

Thanks for your contribution.

I'm certainly interested in your comment... "I'm always a bit nervous of the SUMIF, COUNTIF etc functions performance-wise."

Can you elaborate ? If there's something we all should know about, then it would be GREAT if you would share your concerns. Thanks.

As for "sharing", here's another "offering"... yet another alternative to resolving Keith's task... Again, I've tested the following formula, and it "works like a charm".

=DMIN(data,1,crit)

...where "data" is a Range Name assigned to the database.

...where "crit" is a Range Name assigned to the following criteria...

field1
<>0

While I agree that &quot;array formula&quot; are often very useful, please appreciate that MANY Excel users OVERLOOK the VERY POWERFUL built-in &quot;database functionality&quot; of Excel.

Many Excel users, in fact, do NOT even know that this power EXISTS !!!

Part of the reason for this, is that Excel &quot;Help&quot; unfortunately is not very elaborate in this area. PLUS, there are &quot;quirks&quot; that one has to identify and &quot;work around&quot;.

One example... If a user goes to use &quot;Data - Filter - Advanced Filter&quot;, and attempts to extract data to another worksheet, the user will encounter an error message saying that it's NOT possible. ...NOT true. Indeed it IS possible.

Here's code that does exactly what Excel says is NOT possible...

Range(&quot;data&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=&quot;crit&quot;, _
CopyToRange:=Range(&quot;out&quot, _
Unique:=False

It the above example, &quot;data&quot; is on a SEPARATE sheet from the &quot;output range” - named &quot;out&quot;.

As an example of what I referred to earlier, about MANY Excel users NOT being aware of this POWERFUL &quot;database functionality&quot;... Please refer to thread68-263488 - where you'll find a GOOD example.

Here was a guy who REALLY appreciated &quot;being introduced&quot; to Excel's &quot;database functionality&quot;. And, as you’ll notice if you read the thread, it was really a “shot in the dark” situation where I “dropped the suggestion” that he consider using Excel’s “database functionality”. END RESULT - He was SO impressed, he &quot;dropped&quot; his development of an Access database - in favor of using Excel's database functions - because (he says) in that particular case it made complete sense to do EVERYTHING within Excel - because it's MUCH easier and simpler.

There are, of course, other applications that REQUIRE the use of a TRUE database. However, I hope to convince those MANY Excel users who are still &quot;unaware&quot;, that indeed there are MANY applications that can and should be &quot;self-contained&quot; right WITHIN Excel.

I hope you, and others, appreciate the importance of what I've attempted to describe, and will &quot;take a serious look&quot; at Excel's &quot;database functionality&quot;.

If anyone runs into difficulty, and/or would appreciate receiving example files, please don't hesitate to ask.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[keithvp]

Thank you both for the suggestions. I have tried the original two suggestions, i.e small and array. Both worked fine. I tested the small function containing countif extensively to see if it worked. It did. The Array function worked well too.

One of my concerns is that some of my users will not remember the formulas. I do want to use one of the two formulas within a function itself so that the users only need to provide the function and the range.
But thank you both
Keith