Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel MIN Formula/Function Without Including Zeros

| | Information Helpful? Why Not Donate.

Get Minimum Value in Excel Excluding Zeros. Excel MIN Formula/Function Omitting zeros

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 draw-back 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 k-th 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 k-th 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 Add-ins 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 [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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