# Excel MIN Formula/Function Without Including Zeros

## 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**

**
Excel Trader Package
**Technical Analysis in Excel With $139.00 of **FREE software!**

**Some of our more popular products are below...**

**Convert** Excel** Spreadsheets To Webpages** | **Trading** In Excel | Construction **Estimators** | **Finance** Templates & Add-ins **Bundle **| Code-**VBA**** **| Smart-**VBA** | **Print**-VBA | Excel **Data Manipulation & Analysis** | **Convert** MS Office Applications To...... | **Analyzer** Excel | **Downloader** Excel
| **MSSQL Migration**
Toolkit |
**
Monte Carlo** Add-in |
Excel **
Costing Templates**