Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find first row where condition is true?
Looking at rows 2:4888, I want to find the first row number "x" in which
OR(Hx<Kx,Ix<Kx,Jx<Kx) is true. How can I write that formula in Excel 2003? I tried the following array formula, to no avail: =MIN(IF(OR(H2:H4888<K2:K4888,I2:I4888<K2:K4888,J 2:J4888<K2:K4888),ROW(G2:G4888))) making sure that the condition is first true in row 4. But that formula always returns 2. I confirmed that the formula is an array formula enclosed in curly braces. Also, I filled a parallel column with =OR(H2<K2,I2<K2,J2<K2) and copied down to confirm that the OR function first returns TRUE in row 4. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find first row where condition is true?
In N2: =--AND(K2<"",OR(H2<K2,I2<K2,J2<K2))
Copy down. Leave N1 empty. (I took the liberty to add an additional logic check that col K is not empty, which IMO makes your original OR intents more meaningful) Then you could use in say, M2: =MATCH(1,N:N,0) to get the "first" row number which satisfies the conditions Above helps in some way? Click the YES button below. -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "WhatsUp31415" wrote: Looking at rows 2:4888, I want to find the first row number "x" in which OR(Hx<Kx,Ix<Kx,Jx<Kx) is true. How can I write that formula in Excel 2003? I tried the following array formula, to no avail: =MIN(IF(OR(H2:H4888<K2:K4888,I2:I4888<K2:K4888,J 2:J4888<K2:K4888),ROW(G2:G4888))) making sure that the condition is first true in row 4. But that formula always returns 2. I confirmed that the formula is an array formula enclosed in curly braces. Also, I filled a parallel column with =OR(H2<K2,I2<K2,J2<K2) and copied down to confirm that the OR function first returns TRUE in row 4. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find first row where condition is true?
Maybe this array formula** :
=INDEX(ROW(K:K),MATCH(TRUE,MMULT(--(H2:J4888=K2:K4888),{1;1;1})<3,0)+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "WhatsUp31415" wrote in message ... Looking at rows 2:4888, I want to find the first row number "x" in which OR(Hx<Kx,Ix<Kx,Jx<Kx) is true. How can I write that formula in Excel 2003? I tried the following array formula, to no avail: =MIN(IF(OR(H2:H4888<K2:K4888,I2:I4888<K2:K4888,J 2:J4888<K2:K4888),ROW(G2:G4888))) making sure that the condition is first true in row 4. But that formula always returns 2. I confirmed that the formula is an array formula enclosed in curly braces. Also, I filled a parallel column with =OR(H2<K2,I2<K2,J2<K2) and copied down to confirm that the OR function first returns TRUE in row 4. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find first row where condition is true?
"Max" wrote:
I took the liberty to add an additional logic check that col K is not empty, which IMO makes your original OR intents more meaningful Why? Rhetorical question. Only I can determine the "meaningfulness" of conditions to test. In this case, K2:K4888 is fully populated, just as columns H, I and J are. So the extra condition is irrelevant In N2: =--AND(K2<"",OR(H2<K2,I2<K2,J2<K2)) Copy down. Leave N1 empty. [....] Then you could use in say, M2: =MATCH(1,N:N,0) Well, if I wanted to use helper cells, I would simply use =OR(H2<K2,I2<K2,J2<K2), as I did in my original posting. Computationally more efficient. And I would use =ROW(M1)+MATCH(TRUE,M2:M4888,0) because I want the row number, not simply the array index. I use ROW so that the formula is updated automagically if I insert rows above. Above helps in some way? Not really. I asked for a formula, if possible. If that's not possible, an explanation of why not would be helpful. I guess the reason that my formula failed is that OR accepts an array argument, which foils my attempt to have the formula interpreted as: MIN({IF(OR(H2<K2,I2<K2,J2<K2),ROW(G2), IF(OR(H3<K3,I3<K3,J3<K3),ROW(G3), ...}). Having realized that, I simply need to use an alternative for OR. The following does the job: =MIN(IF((H3:H4889<K3:K4889)+(I3:I4889<K3:K4889)+ (J3:J4889<K3:K4889),ROW(G3:G4889))) (Sorry for the row numbering change. I inserted a row.) ----- original message ----- "Max" wrote in message ... In N2: =--AND(K2<"",OR(H2<K2,I2<K2,J2<K2)) Copy down. Leave N1 empty. (I took the liberty to add an additional logic check that col K is not empty, which IMO makes your original OR intents more meaningful) Then you could use in say, M2: =MATCH(1,N:N,0) to get the "first" row number which satisfies the conditions Above helps in some way? Click the YES button below. -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "WhatsUp31415" wrote: Looking at rows 2:4888, I want to find the first row number "x" in which OR(Hx<Kx,Ix<Kx,Jx<Kx) is true. How can I write that formula in Excel 2003? I tried the following array formula, to no avail: =MIN(IF(OR(H2:H4888<K2:K4888,I2:I4888<K2:K4888,J 2:J4888<K2:K4888),ROW(G2:G4888))) making sure that the condition is first true in row 4. But that formula always returns 2. I confirmed that the formula is an array formula enclosed in curly braces. Also, I filled a parallel column with =OR(H2<K2,I2<K2,J2<K2) and copied down to confirm that the OR function first returns TRUE in row 4. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find first row where condition is true?
Improvement
Don't really need INDEX: =MATCH(TRUE,MMULT(--(H2:J4888=K2:K4888),{1;1;1})<3,0)+1 Still array entered -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Maybe this array formula** : =INDEX(ROW(K:K),MATCH(TRUE,MMULT(--(H2:J4888=K2:K4888),{1;1;1})<3,0)+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "WhatsUp31415" wrote in message ... Looking at rows 2:4888, I want to find the first row number "x" in which OR(Hx<Kx,Ix<Kx,Jx<Kx) is true. How can I write that formula in Excel 2003? I tried the following array formula, to no avail: =MIN(IF(OR(H2:H4888<K2:K4888,I2:I4888<K2:K4888,J 2:J4888<K2:K4888),ROW(G2:G4888))) making sure that the condition is first true in row 4. But that formula always returns 2. I confirmed that the formula is an array formula enclosed in curly braces. Also, I filled a parallel column with =OR(H2<K2,I2<K2,J2<K2) and copied down to confirm that the OR function first returns TRUE in row 4. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find first row where condition is true?
Ah, so. Dismiss my earlier help attempt which is clearly below your
expertise level. I have no further comments. -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "WhatsUp31415" wrote in message ... "Max" wrote: I took the liberty to add an additional logic check that col K is not empty, which IMO makes your original OR intents more meaningful Why? Rhetorical question. Only I can determine the "meaningfulness" of conditions to test. In this case, K2:K4888 is fully populated, just as columns H, I and J are. So the extra condition is irrelevant In N2: =--AND(K2<"",OR(H2<K2,I2<K2,J2<K2)) Copy down. Leave N1 empty. [....] Then you could use in say, M2: =MATCH(1,N:N,0) Well, if I wanted to use helper cells, I would simply use =OR(H2<K2,I2<K2,J2<K2), as I did in my original posting. Computationally more efficient. And I would use =ROW(M1)+MATCH(TRUE,M2:M4888,0) because I want the row number, not simply the array index. I use ROW so that the formula is updated automagically if I insert rows above. Above helps in some way? Not really. I asked for a formula, if possible. If that's not possible, an explanation of why not would be helpful. I guess the reason that my formula failed is that OR accepts an array argument, which foils my attempt to have the formula interpreted as: MIN({IF(OR(H2<K2,I2<K2,J2<K2),ROW(G2), IF(OR(H3<K3,I3<K3,J3<K3),ROW(G3), ...}). Having realized that, I simply need to use an alternative for OR. The following does the job: =MIN(IF((H3:H4889<K3:K4889)+(I3:I4889<K3:K4889)+ (J3:J4889<K3:K4889),ROW(G3:G4889))) (Sorry for the row numbering change. I inserted a row.) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find first row where condition is true?
You cannot use logical functions in an array formula
This gives the row of the first line where H, I OR J do not equal K =MIN(IF(((H2:H14<K2:K14)+(I2:I14<K2:K14)+(J2:J14 <K2:K14)),ROW(K2:K14),10^99)) enter as array formula, of course change + to - to find the fist row where H, I AND J do not equal K best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "WhatsUp31415" wrote in message ... Looking at rows 2:4888, I want to find the first row number "x" in which OR(Hx<Kx,Ix<Kx,Jx<Kx) is true. How can I write that formula in Excel 2003? I tried the following array formula, to no avail: =MIN(IF(OR(H2:H4888<K2:K4888,I2:I4888<K2:K4888,J 2:J4888<K2:K4888),ROW(G2:G4888))) making sure that the condition is first true in row 4. But that formula always returns 2. I confirmed that the formula is an array formula enclosed in curly braces. Also, I filled a parallel column with =OR(H2<K2,I2<K2,J2<K2) and copied down to confirm that the OR function first returns TRUE in row 4. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Rows If Condition True | Excel Worksheet Functions | |||
result if a condition is NOT true? | Excel Worksheet Functions | |||
Continually check if condition is true in VBA | Excel Discussion (Misc queries) | |||
TRUE condition??? | Excel Discussion (Misc queries) | |||
How Do I Hide A Row (if a condition is true) using a Macro ? | Excel Worksheet Functions |