 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2006small 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,"") > Hi All, > [quoted text clipped - 6 lines] > > 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? > in a2 enter : > [quoted text clipped - 10 lines] > > > > SPL How 'bout (in cell A2) and copy down: =IF(B2<>B1,"Y","") > 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 [quoted text clipped - 28 lines] >> > >> > 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. > How 'bout (in cell A2) and copy down: > =IF(B2<>B1,"Y","") [quoted text clipped - 31 lines] > >> > > >> > SPL Try... A2, copied down: =IF(COUNTIF($B$2:B2,B2)=1,"Y","") Hope this helps! > 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 [quoted text clipped - 12 lines] > 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? > Try... > [quoted text clipped - 20 lines] > > 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? 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! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.