Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,104
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hide Rows If Condition True wilbursj Excel Worksheet Functions 2 April 20th 09 08:09 PM
result if a condition is NOT true? Roland Excel Worksheet Functions 2 March 7th 07 10:25 AM
Continually check if condition is true in VBA [email protected] Excel Discussion (Misc queries) 1 October 4th 06 05:43 PM
TRUE condition??? pmguerra Excel Discussion (Misc queries) 2 June 5th 06 04:27 PM
How Do I Hide A Row (if a condition is true) using a Macro ? Anthony Fantone Excel Worksheet Functions 1 June 16th 05 04:54 PM


All times are GMT +1. The time now is 09:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"