small problem

  • Thread starter Thread starter PH NEWS
  • Start date Start date
P

PH NEWS

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?
 
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.
 
Try...

A2, copied down:

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

Hope this helps!
 
PH NEWS said:
fantastic, cheers. What's the =1 bit about, how does it work?
Click to expand...

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!
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formulas Still Not Working 5
IF function 3
Conditionally summing data from two columns depending on their value. 3
Sorting the cells of a formula causes the formula to not work 3
Lookup for the first occurrence 4
Payment calculation 1
finding the earliest date in an array of cells 6
First Occurence of Non Blank Cell in row array 4

Back
Top