small problem
 

Hi All,

How do you write into an if function, "= any", for example from b2:b20 I
have a list of numbers, in a2 I would like an if function to do this: if b2
= any of b3:b20 = 1. Has it got something to do with &?

cheers

SPL

small problem
 

in a2 enter :

=IF(COUNTIF(B3:B20,B2)0,1,"")


"PH NEWS" wrote in message
...
Hi All,

How do you write into an if function, "= any", for example from b2:b20 I
have a list of numbers, in a2 I would like an if function to do this: if
b2
= any of b3:b20 = 1. Has it got something to do with &?

cheers

SPL

small problem
 

Thanks, but that's not really what I'm looking for. I'll try to explain
further. In B2:B20 I have a list like so
1
1
1
2
2
2
2
3
3
4
4
and so on. In column A, I'd like a value to appear next to the first
occurrence of a number, so A2 would say "Y" and then the next "Y" would
appear next to the first 2 and so on. Is that possible?
"Jim May" wrote in message news:r9bUf.51$KE1.35@dukeread02...
in a2 enter :

=IF(COUNTIF(B3:B20,B2)0,1,"")


"PH NEWS" wrote in message
...
Hi All,

How do you write into an if function, "= any", for example from b2:b20 I
have a list of numbers, in a2 I would like an if function to do this: if
b2
= any of b3:b20 = 1. Has it got something to do with &?

cheers

SPL

small problem
 

How 'bout (in cell A2) and copy down:
=IF(B2<B1,"Y","")


"PH NEWS" wrote in message
...
Thanks, but that's not really what I'm looking for. I'll try to explain
further. In B2:B20 I have a list like so
1
1
1
2
2
2
2
3
3
4
4
and so on. In column A, I'd like a value to appear next to the first
occurrence of a number, so A2 would say "Y" and then the next "Y" would
appear next to the first 2 and so on. Is that possible?
"Jim May" wrote in message
news:r9bUf.51$KE1.35@dukeread02...
in a2 enter :

=IF(COUNTIF(B3:B20,B2)0,1,"")


"PH NEWS" wrote in message
...
Hi All,

How do you write into an if function, "= any", for example from b2:b20
I
have a list of numbers, in a2 I would like an if function to do this:
if
b2
= any of b3:b20 = 1. Has it got something to do with &?

cheers

SPL

small problem
 

Yeah, tried that one, but that only works if the range stays in numerical
order, if it goes like this,
1
1
2
2
2
3
1
then that formula doesn't work.
"Jim May" wrote in message
news:5lcUf.353$KE1.125@dukeread02...
How 'bout (in cell A2) and copy down:
=IF(B2<B1,"Y","")


"PH NEWS" wrote in message
...
Thanks, but that's not really what I'm looking for. I'll try to explain
further. In B2:B20 I have a list like so
1
1
1
2
2
2
2
3
3
4
4
and so on. In column A, I'd like a value to appear next to the first
occurrence of a number, so A2 would say "Y" and then the next "Y" would
appear next to the first 2 and so on. Is that possible?
"Jim May" wrote in message
news:r9bUf.51$KE1.35@dukeread02...
in a2 enter :

=IF(COUNTIF(B3:B20,B2)0,1,"")


"PH NEWS" wrote in message
...
Hi All,

How do you write into an if function, "= any", for example from
b2:b20
I
have a list of numbers, in a2 I would like an if function to do this:
if
b2
= any of b3:b20 = 1. Has it got something to do with &?

cheers

SPL

small problem
 

Try...

A2, copied down:

=IF(COUNTIF($B$2:B2,B2)=1,"Y","")

Hope this helps!

In article ,
"PH NEWS" wrote:

Thanks, but that's not really what I'm looking for. I'll try to explain
further. In B2:B20 I have a list like so
1
1
1
2
2
2
2
3
3
4
4
and so on. In column A, I'd like a value to appear next to the first
occurrence of a number, so A2 would say "Y" and then the next "Y" would
appear next to the first 2 and so on. Is that possible?

small problem
 

fantastic, cheers. What's the =1 bit about, how does it work?
"Domenic" wrote in message
...
Try...

A2, copied down:

=IF(COUNTIF($B$2:B2,B2)=1,"Y","")

Hope this helps!

In article ,
"PH NEWS" wrote:

Thanks, but that's not really what I'm looking for. I'll try to explain
further. In B2:B20 I have a list like so
1
1
1
2
2
2
2
3
3
4
4
and so on. In column A, I'd like a value to appear next to the first
occurrence of a number, so A2 would say "Y" and then the next "Y" would
appear next to the first 2 and so on. Is that possible?

small problem
 

In article ,
"PH NEWS" wrote:

fantastic, cheers. What's the =1 bit about, how does it work?

You'll notice that as you copy the formula to other cells in the column,
both the range and criteria change. Anytime COUNTIF equals 1, the IF
statement will evaluate to TRUE and returns 'Y'. If COUNTIF equals an
amount greater than 1, the IF statement will evaluate to FALSE and the
cell is left blank.

Hope this helps!