If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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$2190),$ 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 |
Thread Tools | |
Display Modes | |
|
|