Return the minimum number in a range excluding zero

[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

 

[Don Guillett]

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))

 

[Guest]

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.

 

[Ron Coderre]

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)