software

Hi All,

How do you write into an if function, quot;= anyquot;, 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 amp;?

cheers

SPL
in a2 enter :

=IF(COUNTIF(B3:B20,B2)gt;0,1,quot;quot;)quot;PH NEWSquot; gt; wrote in message
...
gt; Hi All,
gt;
gt; How do you write into an if function, quot;= anyquot;, for example from b2:b20 I
gt; have a list of numbers, in a2 I would like an if function to do this: if
gt; b2
gt; = any of b3:b20 = 1. Has it got something to do with amp;?
gt;
gt; cheers
gt;
gt; SPL
gt;
gt;
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 quot;Yquot; and then the next quot;Yquot; would
appear next to the first 2 and so on. Is that possible?
quot;Jim Mayquot; gt; wrote in message news:r9bUf.51$KE1.35@dukeread02...
gt; in a2 enter :
gt;
gt; =IF(COUNTIF(B3:B20,B2)gt;0,1,quot;quot;)
gt;
gt;
gt; quot;PH NEWSquot; gt; wrote in message
gt; ...
gt; gt; Hi All,
gt; gt;
gt; gt; How do you write into an if function, quot;= anyquot;, for example from b2:b20 I
gt; gt; have a list of numbers, in a2 I would like an if function to do this: if
gt; gt; b2
gt; gt; = any of b3:b20 = 1. Has it got something to do with amp;?
gt; gt;
gt; gt; cheers
gt; gt;
gt; gt; SPL
gt; gt;
gt; gt;
gt;
gt;
How 'bout (in cell A2) and copy down:
=IF(B2lt;gt;B1,quot;Yquot;,quot;quot;)quot;PH NEWSquot; gt; wrote in message
...
gt; Thanks, but that's not really what I'm looking for. I'll try to explain
gt; further. In B2:B20 I have a list like so
gt; 1
gt; 1
gt; 1
gt; 2
gt; 2
gt; 2
gt; 2
gt; 3
gt; 3
gt; 4
gt; 4
gt; and so on. In column A, I'd like a value to appear next to the first
gt; occurrence of a number, so A2 would say quot;Yquot; and then the next quot;Yquot; would
gt; appear next to the first 2 and so on. Is that possible?
gt; quot;Jim Mayquot; gt; wrote in message
gt; news:r9bUf.51$KE1.35@dukeread02...
gt;gt; in a2 enter :
gt;gt;
gt;gt; =IF(COUNTIF(B3:B20,B2)gt;0,1,quot;quot;)
gt;gt;
gt;gt;
gt;gt; quot;PH NEWSquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hi All,
gt;gt; gt;
gt;gt; gt; How do you write into an if function, quot;= anyquot;, for example from b2:b20
gt;gt; gt; I
gt;gt; gt; have a list of numbers, in a2 I would like an if function to do this:
gt;gt; gt; if
gt;gt; gt; b2
gt;gt; gt; = any of b3:b20 = 1. Has it got something to do with amp;?
gt;gt; gt;
gt;gt; gt; cheers
gt;gt; gt;
gt;gt; gt; SPL
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;
gt;
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.
quot;Jim Mayquot; gt; wrote in message
news:5lcUf.353$KE1.125@dukeread02...
gt; How 'bout (in cell A2) and copy down:
gt; =IF(B2lt;gt;B1,quot;Yquot;,quot;quot;)
gt;
gt;
gt; quot;PH NEWSquot; gt; wrote in message
gt; ...
gt; gt; Thanks, but that's not really what I'm looking for. I'll try to explain
gt; gt; further. In B2:B20 I have a list like so
gt; gt; 1
gt; gt; 1
gt; gt; 1
gt; gt; 2
gt; gt; 2
gt; gt; 2
gt; gt; 2
gt; gt; 3
gt; gt; 3
gt; gt; 4
gt; gt; 4
gt; gt; and so on. In column A, I'd like a value to appear next to the first
gt; gt; occurrence of a number, so A2 would say quot;Yquot; and then the next quot;Yquot; would
gt; gt; appear next to the first 2 and so on. Is that possible?
gt; gt; quot;Jim Mayquot; gt; wrote in message
gt; gt; news:r9bUf.51$KE1.35@dukeread02...
gt; gt;gt; in a2 enter :
gt; gt;gt;
gt; gt;gt; =IF(COUNTIF(B3:B20,B2)gt;0,1,quot;quot;)
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;PH NEWSquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; Hi All,
gt; gt;gt; gt;
gt; gt;gt; gt; How do you write into an if function, quot;= anyquot;, for example from
b2:b20
gt; gt;gt; gt; I
gt; gt;gt; gt; have a list of numbers, in a2 I would like an if function to do this:
gt; gt;gt; gt; if
gt; gt;gt; gt; b2
gt; gt;gt; gt; = any of b3:b20 = 1. Has it got something to do with amp;?
gt; gt;gt; gt;
gt; gt;gt; gt; cheers
gt; gt;gt; gt;
gt; gt;gt; gt; SPL
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;
Try...

A2, copied down:

=IF(COUNTIF($B$2:B2,B2)=1,quot;Yquot;,quot;quot;)

Hope this helps!

In article gt;,
quot;PH NEWSquot; gt; wrote:

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

fantastic, cheers. What's the =1 bit about, how does it work?
quot;Domenicquot; gt; wrote in message
...
gt; Try...
gt;
gt; A2, copied down:
gt;
gt; =IF(COUNTIF($B$2:B2,B2)=1,quot;Yquot;,quot;quot;)
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;PH NEWSquot; gt; wrote:
gt;
gt; gt; Thanks, but that's not really what I'm looking for. I'll try to explain
gt; gt; further. In B2:B20 I have a list like so
gt; gt; 1
gt; gt; 1
gt; gt; 1
gt; gt; 2
gt; gt; 2
gt; gt; 2
gt; gt; 2
gt; gt; 3
gt; gt; 3
gt; gt; 4
gt; gt; 4
gt; gt; and so on. In column A, I'd like a value to appear next to the first
gt; gt; occurrence of a number, so A2 would say quot;Yquot; and then the next quot;Yquot; would
gt; gt; appear next to the first 2 and so on. Is that possible?
In article gt;,
quot;PH NEWSquot; gt; wrote:

gt; 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!