Prev: Randomizing
Next: Opening xlsx files in Excel 2000
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: Randomizing
Next: Opening xlsx files in Excel 2000