Welcome toExcel.Tips.Net
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Learn Access Now
Free Printable Forms
Beauty Tips
Car Tips
Cleaning Tips
Cooking Tips
ExcelTips (menu)
ExcelTips (ribbon)
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
School Tips
Wedding Tips
WordTips (menu)
WordTips (ribbon)
Advertise on the
ExcelTips Site
Working with Imperial Linear Distances
Incomplete and Corrupt Sorting
Quickly Removing a Toolbar Button
Deriving High and Low Non-Zero Values
Counting Cells with Specific Characters
There may be times when you need to derive the smallest (or largest) value from a range, unless the smallest (or largest) value is zero. For instance, you might have a range of values such as {0, 3, 1, 4, 2}. In this case, the lowest value is zero, but the value you really want returned is 1.
There is no intrinsic function within Excel to return a value as stipulated here. However, you can create a formula that will do the trick. Assuming that the range of values you want to analyze are in C4:C8, the following formula will return the lowest non-zero value:
=IF(MIN(C4:C8)=0,SMALL(C4:C8,COUNTIF(C4:C8,"=0")+1),MIN(C4:C8))
This formula uses the MIN function to determine if the lowest value in the range is zero. If it is, then the SMALL function is used to derive the lowest value, excluding the zeros. (The COUNTIF function returns the number of zeros in the range, and therefore tells SMALL which item from the range to pick.)
A small change to the formula allows it to be used to return the largest non-zero number in a range:
=IF(MAX(C4:C8)=0,LARGE(C4:C8,COUNTIF(C4:C8,"=0")+1),MAX(C4:C8))
These formulas will work for any range, unless the range is made up entirely of zeros. In that instance, a #NUM! error is returned.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2332) applies to Microsoft Excel versions: 97 2000 2002 2003
Save Time! ExcelTips has been published weekly since late 1998. Past issues of ExcelTips are available in convenient ExcelTips archives. Have your own enhanced archive of ExcelTips at your fingertips, available to use at any time!