
Current Special! Complete Excel
Excel
Training Course
for Excel 97  Excel 2003, only $145.00.
$59.95 Instant
Buy/Download,
30 Day Money Back Guarantee & Free
Excel Help for LIFE!
Back to Excel Formulas . Got any Excel Questions? Free Excel Help
Excel MIN Without Zeros
See Also
AVERAGE Excluding Zeros
Excel has a built in formula/function that makes finding the minimum value
in a range of cells
easy. If we assume your numbers are in A1:A100 you would simply use the
MIN formula like shown below;
=MIN(A1:A100)
There is however, one drawback with this. That is, it includes cells that
contain 0 (zeros). This can give you unexpected results. So how do we omit zeros from our average?
SMALL Function/Formula
By far the most efficient method is to use the SMALL and
COUNTIF formula
as shown below;
SMALL Returns the kth smallest
value in a data set.
=SMALL(A1:A100,COUNTIF($A$1:$A$100,0)+1)
Where the countif is counting
the zeros in the range (+1) and is used to tell SMALL to return the kth
smallest value.
DMIN
The other method is via the DMIN function. This function is part of the
Database functions
and all are extremely useful when/if you need
specify multiple criteria. The DMIN , in the case of numbers being in
A2:A100 (A1 should be a heading) we could use the DMIN like below;
=DMIN($A$1:$A$100,1,$B$1:$B$2)
Where "1" represents the relative column position to average in the range
A1:A100
B1 has an exact copy of your heading in A1
B2 houses the expression >0 or <>0 if have negative numbers you want
included.
Array MIN
This method is the least efficient. By creating an
array formula
we
can use the MIN formula as shown below to omit zeros;
=MIN(IF($A$1:$A$1004 >0,$A$1:$A$100))
=MIN(IF($A$1:$A$1004 <>0,$A$1:$A$100))
As these are array formulas they MUST be entered via Ctrl+Shift+Enter.
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Addins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages  Trading In Excel  Construction Estimators  Finance Templates & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates