Return the minimum number in a range excluding zero

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I am currently using the following to find the maximum value in a
range($G$120:$G$219), when the corresponding value in range ($H$120:$H$219)
is equal to a specific value.
=SUMPRODUCT(MAX(($H$120:$H$219=DF120)*($G$120:$G$219)))

My problem is that when i try to find the minimum by substuting the MAX()
for MIN() i always get a Zero.

Can anyone suggest how to return the lowest value that is not zero
 
One way with an ARRAY formula. You may have to use .000001
=INDEX(J2:J22,MATCH(MIN(IF(H2:H22>0.00001,H2:H22)),H2:H22))
 
Try:
=MIN(IF(($G$120:$G$219)<>0,($H$120:$H$219=DF120)*$G$120:$G$219))

Entered as an array formula using Ctrl+Shift+Enter.

You should get {} brackets round the formula if it is entered correctly.
 
Try this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just
ENTER:
=MIN(IF($H$120:$H$219=DF120,$G$120:$G$219))

or....maybe this (to exclude any Col_G zero values:
=MIN(IF(($H$120:$H$219=DF120)*($G$120:$G$219<>0),$G$120:$G$219))

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
Back
Top