|
min if
MIN(IF(A2:A43="d",B2:BG43)) entered as an array
Does it return a 0 because of the blank cell? And how do I ignore the blank cell? a2 b2 c2 d blank 120 d 100 d 150 175 a 75 90 Thanks |
min if
"Rene" wrote:
MIN(IF(A2:A43="d",B2:BG43)) entered as an array [....] how do I ignore the blank cell? You probably already discovered that the "obvious" solution does __not__ work, namely the following array formula: =MIN(IF(AND(A2:A43="d",B2:BG43<""),B2:BG43)) But the following array formula expresses the same logic: =MIN(IF((A2:A43="d")*(B2:BG43<""),B2:BG43)) ----- original message ----- "Rene" wrote in message ... MIN(IF(A2:A43="d",B2:BG43)) entered as an array Does it return a 0 because of the blank cell? And how do I ignore the blank cell? a2 b2 c2 d blank 120 d 100 d 150 175 a 75 90 Thanks |
min if
Working with just your smaller data set I used
=MIN(IF(A2:A5="d",IF(B2:C50,B2:C5))) and got 100 as expected Of course it must be array entered with CTRL+SHIFT+ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rene" wrote in message ... MIN(IF(A2:A43="d",B2:BG43)) entered as an array Does it return a 0 because of the blank cell? And how do I ignore the blank cell? a2 b2 c2 d blank 120 d 100 d 150 175 a 75 90 Thanks |
min if
=MIN(IF(AND(A2:A43="d",B2:BG43<""),B2:BG43)) This returns the min in
B2:BG43. I'm looking for the min in B2:BG43 with a "d" in A2:A43. Thanks "JoeU2004" wrote: "Rene" wrote: MIN(IF(A2:A43="d",B2:BG43)) entered as an array [....] how do I ignore the blank cell? You probably already discovered that the "obvious" solution does __not__ work, namely the following array formula: =MIN(IF(AND(A2:A43="d",B2:BG43<""),B2:BG43)) But the following array formula expresses the same logic: =MIN(IF((A2:A43="d")*(B2:BG43<""),B2:BG43)) ----- original message ----- "Rene" wrote in message ... MIN(IF(A2:A43="d",B2:BG43)) entered as an array Does it return a 0 because of the blank cell? And how do I ignore the blank cell? a2 b2 c2 d blank 120 d 100 d 150 175 a 75 90 Thanks |
min if
"Rene" wrote:
=MIN(IF(AND(A2:A43="d",B2:BG43<""),B2:BG43)) This returns the min in B2:BG43. I'm looking for the min in B2:BG43 with a "d" in A2:A43. You misunderstood. I said that formula does __not__ work. You should use the other formula: =MIN(IF((A2:A43="d")*(B2:BG43<""),B2:BG43)) I presented the first formula (with AND) to help you understand the second formula. ----- original message ----- "Rene" wrote in message ... =MIN(IF(AND(A2:A43="d",B2:BG43<""),B2:BG43)) This returns the min in B2:BG43. I'm looking for the min in B2:BG43 with a "d" in A2:A43. Thanks "JoeU2004" wrote: "Rene" wrote: MIN(IF(A2:A43="d",B2:BG43)) entered as an array [....] how do I ignore the blank cell? You probably already discovered that the "obvious" solution does __not__ work, namely the following array formula: =MIN(IF(AND(A2:A43="d",B2:BG43<""),B2:BG43)) But the following array formula expresses the same logic: =MIN(IF((A2:A43="d")*(B2:BG43<""),B2:BG43)) ----- original message ----- "Rene" wrote in message ... MIN(IF(A2:A43="d",B2:BG43)) entered as an array Does it return a 0 because of the blank cell? And how do I ignore the blank cell? a2 b2 c2 d blank 120 d 100 d 150 175 a 75 90 Thanks |
min if
PS....
I wrote: You should use the other formula: =MIN(IF((A2:A43="d")*(B2:BG43<""),B2:BG43)) Alternatively: =MIN(IF(A2:A43="d",IF(B2:BG43<"",B2:BG43))) That might be easier to understand. However, beware that that does not scale well due to nesting limits before Excel 2007. That is, the number of ANDed conditions is limited to 7, and it is difficult to combine ANDed and ORed conditions. ----- original message ----- "JoeU2004" wrote in message ... "Rene" wrote: =MIN(IF(AND(A2:A43="d",B2:BG43<""),B2:BG43)) This returns the min in B2:BG43. I'm looking for the min in B2:BG43 with a "d" in A2:A43. You misunderstood. I said that formula does __not__ work. You should use the other formula: =MIN(IF((A2:A43="d")*(B2:BG43<""),B2:BG43)) I presented the first formula (with AND) to help you understand the second formula. ----- original message ----- "Rene" wrote in message ... =MIN(IF(AND(A2:A43="d",B2:BG43<""),B2:BG43)) This returns the min in B2:BG43. I'm looking for the min in B2:BG43 with a "d" in A2:A43. Thanks "JoeU2004" wrote: "Rene" wrote: MIN(IF(A2:A43="d",B2:BG43)) entered as an array [....] how do I ignore the blank cell? You probably already discovered that the "obvious" solution does __not__ work, namely the following array formula: =MIN(IF(AND(A2:A43="d",B2:BG43<""),B2:BG43)) But the following array formula expresses the same logic: =MIN(IF((A2:A43="d")*(B2:BG43<""),B2:BG43)) ----- original message ----- "Rene" wrote in message ... MIN(IF(A2:A43="d",B2:BG43)) entered as an array Does it return a 0 because of the blank cell? And how do I ignore the blank cell? a2 b2 c2 d blank 120 d 100 d 150 175 a 75 90 Thanks |
min if
On Jul 6, 4:51*am, "JoeU2004" wrote:
PS.... I wrote: You should use the other formula: =MIN(IF((A2:A43="d")*(B2:BG43<""),B2:BG43)) Alternatively: =MIN(IF(A2:A43="d",IF(B2:BG43<"",B2:BG43))) That might be easier to understand. *However, beware that that does not scale well due to nesting limits before Excel 2007. *That is, the number of ANDed conditions is limited to 7, and it is difficult to combine ANDed and ORed conditions. ----- original message ----- "JoeU2004" wrote in message ... "Rene" wrote: =MIN(IF(AND(A2:A43="d",B2:BG43<""),B2:BG43)) This returns the min in B2:BG43. *I'm looking for the min in B2:BG43 with a "d" in A2:A43. You misunderstood. *I said that formula does __not__ work. You should use the other formula: =MIN(IF((A2:A43="d")*(B2:BG43<""),B2:BG43)) I presented the first formula (with AND) to help you understand the second formula. ----- original message ----- "Rene" wrote in message ... =MIN(IF(AND(A2:A43="d",B2:BG43<""),B2:BG43)) This returns the min in B2:BG43. *I'm looking for the min in B2:BG43 with a "d" in A2:A43. Thanks "JoeU2004" wrote: "Rene" wrote: MIN(IF(A2:A43="d",B2:BG43)) entered as an array [....] how do I ignore the blank cell? You probably already discovered that the "obvious" solution does __not__ work, namely the following array formula: =MIN(IF(AND(A2:A43="d",B2:BG43<""),B2:BG43)) But the following array formula expresses the same logic: =MIN(IF((A2:A43="d")*(B2:BG43<""),B2:BG43)) ----- original message ----- "Rene" wrote in message ... MIN(IF(A2:A43="d",B2:BG43)) entered as an array Does it return a 0 because of the blank cell? And how do I ignore the blank cell? a2 b2 * * * * *c2 d * blank * *120 d * 100 d * 150 * * *175 a * * 75 * * * *90 Thanks Hi - I was not part of this discussion, but your thread has just helped me A LOT - massively saved face at my client - thanks for your help all! JohnOnTheInternet |
| All times are GMT +1. The time now is 07:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com