From: Meeru on 20 Mar 2010 12:05 hi, I have data in Col A and i would like to get the data in Col B only if the value in Col A is greater than 3 and less than 5 . A B 1 2 2 3 3 4 4 5 6 I am looking for a formula. Any ideas on this please. best regards, Meeru From: Mike H on 20 Mar 2010 12:11 try this =IF(AND(A1>3,A1<5),A1,"") -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Meeru" wrote: > hi, > I have data in Col A and i would like to get the data in Col B only if the > value in Col A is greater than 3 and less than 5 . > > > A B > 1 2 > 2 3 > 3 4 > 4 > 5 > 6 > > I am looking for a formula. Any ideas on this please. > best regards, > Meeru From: מיכאל (מיקי) אבידן on 20 Mar 2010 13:41 Why did you type 2,3,4 while the one and only answer, to the presented data, is: 4 ? Micky "Meeru" wrote: > hi, > I have data in Col A and i would like to get the data in Col B only if the > value in Col A is greater than 3 and less than 5 . > > > A B > 1 2 > 2 3 > 3 4 > 4 > 5 > 6 > > I am looking for a formula. Any ideas on this please. > best regards, > Meeru From: my-oh-my on 21 Mar 2010 16:13 > I have data in Col A and i would like to get the data in Col B only if the> value in Col A is greater than 3 and less than 5 . Here's one way, assuming the data starts in row 2. It uses column C as a helper column. In C2, put =IF(AND(A2>3,A2<5),MAX(C\$1:C1)+1,"") In B2, put =IF(ROW()-1>MAX(C:C),"", OFFSET(\$A\$1,MATCH(ROW()-1,C:C,0)-1,0)) Select B2:C2 and copy down as far as column A can reach. Row 1 and column C can be hidden, if desired. Modify to suit.  |  Pages: 1 Prev: RandomizingNext: Opening xlsx files in Excel 2000