How do I find the minimum value in a range while ignoring zeros?

  • Thread starter Thread starter Ted B.
  • Start date Start date
T

Ted B.

-- How do I find the minimum value in a range while ignoring any zeros in
that range using Excel 2007?
 
If the numbers are *always* positive..

Array entered**:

..=MIN(IF(A1:A10>0,A1:A10))

Or, normally entered:

=SMALL(A1:A10,COUNTIF(A1:A10,0)+1)

If there might be negative numbers...

Array entered**:

=MIN(IF(A1:A10<>0,A1:A10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
You could use a conditional MIN, something like this in say B2, array-entered
ie press CTRL+SHIFT+ENTER to confirm the formula (instead of just pressing
ENTER):
=MIN(IF(A2:A10>0,A2:A10))
Success? hit the YES below
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using MIN function 3
Divide by the minimum non-zero number in a range of cells 21
Return the minimum number in a range excluding zero 3
minimum function 3
Trying to find additional ranges outside of min, max, and average 2
Finding 2nd smallest number in range 9
calculation to ignore a 0 (zero) value 3
minimum value in a column, which row no? 6

Back
Top