Return the minimum number in a range excluding zero
 

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$2 19)))

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

Return the minimum number in a range excluding zero
 

One way with an ARRAY formula. You may have to use .000001
=INDEX(J2:J22,MATCH(MIN(IF(H2:H220.00001,H2:H22)) ,H2:H22))


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jive" wrote in message
...
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$2 19)))

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

Return the minimum number in a range excluding zero
 

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.

"Jive" wrote:

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$2 19)))

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

Return the minimum number in a range excluding zero
 

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)

"Jive" wrote in message
...
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$2 19)))

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