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

in a2 enter :

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


"PH NEWS" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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" <[email protected]> wrote in message news:r9bUf.51$KE1.35@dukeread02...
> in a2 enter :
>
> =IF(COUNTIF(B3:B20,B2)>0,1,"")
>
>
> "PH NEWS" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>

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


"PH NEWS" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:r9bUf.51$KE1.35@dukeread02...
>> in a2 enter :
>>
>> =IF(COUNTIF(B3:B20,B2)>0,1,"")
>>
>>
>> "PH NEWS" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>> >
>>
>>
>
>

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" <[email protected]> wrote in message
news:5lcUf.353$KE1.125@dukeread02...
> How 'bout (in cell A2) and copy down:
> =IF(B2<>B1,"Y","")
>
>
> "PH NEWS" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:r9bUf.51$KE1.35@dukeread02...
> >> in a2 enter :
> >>
> >> =IF(COUNTIF(B3:B20,B2)>0,1,"")
> >>
> >>
> >> "PH NEWS" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >
> >> >
> >>
> >>
> >
> >
>
>

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" <[email protected]> wrote in message
news:5lcUf.353$KE1.125@dukeread02...
> How 'bout (in cell A2) and copy down:
> =IF(B2<>B1,"Y","")
>
>
> "PH NEWS" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:r9bUf.51$KE1.35@dukeread02...
> >> in a2 enter :
> >>
> >> =IF(COUNTIF(B3:B20,B2)>0,1,"")
> >>
> >>
> >> "PH NEWS" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Try...

A2, copied down:

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

Hope this helps!

In article <[email protected]>,
"PH NEWS" <[email protected]> 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?

fantastic, cheers. What's the =1 bit about, how does it work?
"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Try...
>
> A2, copied down:
>
> =IF(COUNTIF($B$2:B2,B2)=1,"Y","")
>
> Hope this helps!
>
> In article <[email protected]>,
> "PH NEWS" <[email protected]> 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?

In article <[email protected]>,
"PH NEWS" <[email protected]> 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!