 |
 | 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 / New Users / December 2007Greater than 1 or 2
I have a column of numbers 1,2,3,5,6,7,8,10,11,12,14 etc.. and would like to create a formula with an if statement that if the number in the cell below is greater by 1 the result is x, if the number in the cell below is greater by 2 the result is y. Thanks Tom Try this... Assume your numbers start in cell A1. Enter this formula in B2 and copy down to the end of data in column A: =IF(A1+1=A2,"x",IF(A1+2=A2,"y",""))  SignatureBiff Microsoft Excel MVP >I have a column of numbers 1,2,3,5,6,7,8,10,11,12,14 etc.. and would > like to create a formula with an if statement that if the number in [quoted text clipped - 4 lines] > > Tom If your numbers are in A1, A2, A3, ... enter into B2: =CHOOSE(A2-A1,x,y) and copy down. Regards, Bernd Missing the quotes: =CHOOSE(A2-A1,"x","y") That a nice solution if those are the only 2 conditions. Note that any difference >2 returns an error. SignatureBiff Microsoft Excel MVP > If your numbers are in A1, A2, A3, ... enter into B2: > =CHOOSE(A2-A1,x,y) > and copy down. > > Regards, > Bernd > =CHOOSE(A2-A1,"x","y") > > That a nice solution if those are the only 2 conditions. Note that any > difference >2 returns an error. I did not get the impression that "x" and "y" were what the OP was actually looking for; but, if those two letters were in fact what was being sought, then this formula will suppress the error (nothing is returned if the difference is not 1 or 2)... =MID("xy",A2-A1+3*(A1=A2),A2-A1) Rick >I did not get the impression that "x" and "y" were what the OP was actually >looking for After reading the post again I think you're probably right. Another advantage of CHOOSE is it's easily expanded for additional conditions (up to a point). SignatureBiff Microsoft Excel MVP >> =CHOOSE(A2-A1,"x","y") >> [quoted text clipped - 9 lines] > > Rick > Another advantage of CHOOSE is it's easily expanded for additional > conditions (up to a point). My formula can be expanded quite easily too <g> =MID("abcxyz",A2-A1+999*OR(A1=A2,A2=""),1) Of course, this again assumes single character returns from the evaluation are what is wanted. As long as the choice string will never be longer than 99 characters (which could obviously be increased if needed), just put the choice string characters in between the quote marks. (Note I corrected a minor error from my first posting that didn't affect the original outcome given the choices proposed by the OP; and I made it more robust at handling some error situations.) Rick The 999 inside the formula was supposed to have been changed to 99 to match the text description I provided. Rick >> Another advantage of CHOOSE is it's easily expanded for additional >> conditions (up to a point). [quoted text clipped - 12 lines] > > Rick |
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.