 |
 | 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 / March 2008Formula - If then else
Using Excel 2002: SP2 Greetings, I have not used Excel at any depth in for several years so I am rusty with formulas, etc. I wish to set up a formula so that When column B = "NNE" column C = "N" When column B = "ENE" column C = "NE" When column B = "NNW" column C = "N" When column B = "WNW" column C = "NW" etc Basically I want to reduce 16 compass points to 8 but the groupings may be based more on preference than what logic would suggest. I have never used a VB script and don't even know how to set up one though I am an experienced programmer (HP Basic, C/C++, etc). Thanks, gtb =LOOKUP(B21,{"NNE","ENE","NNW","WNW"},{"N","NE","N","NW"})  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Using Excel 2002: SP2 > [quoted text clipped - 17 lines] > > gtb I was hoping cases not listed would fall through unchanged but it appears not. So I put in the following formula which translates N to E and E to #N/A, etc. What stupid thing am I doing wrong?" Does the source column need quotes? =LOOKUP(C1, {"N","NNE","NE","ENE","E","ESE","SE","SSE","S","SSW","SW","WSW","W","WNW","NW","NNW"}, {"N","N","NE","E","E","E","SE","S","S","S","SW","W","W","W","NW","N"}) > =LOOKUP(B21,{"NNE","ENE","NNW","WNW"},{"N","NE","N","NW"}) > [quoted text clipped - 3 lines] > > Bob It can do what you want =IF(ISNA(MATCH(C1,{"ENE","NNE","NNW","WNW"},0)),C1, LOOKUP(C1,{"ENE","NNE","NNW","WNW"},{"NE","N","N","NW"})) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >I was hoping cases not listed would fall through unchanged but it > appears not. So I put in the following formula which translates N to E [quoted text clipped - 12 lines] >> >> Bob BTW, your problem was my fault, LOOKUP needs the lookup data in order. So your formula would be =LOOKUP(C1, {"E","ENE","ESE","N","NE","NNE","NNW","NW","S","SE","SSE","SSW","SW","W","WNW","WSW"}, {"E","E","E","N","NE","N","N","NW","S","SE","S","S","SW","W","W","W"}) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > It can do what you want > [quoted text clipped - 17 lines] >>> >>> Bob Thanks again, all smiles now. gtb Your fault? Nay, not when I think of how far along I would be all by my lonesome. gtb > BTW, your problem was my fault, LOOKUP needs the lookup data in order. So > your formula would be Make a lookup table like this Col A: NNE ENE NNW WNW Col B: N NE N NW etc. With the compass point to lookup in C1: =VLOOKUP(C1,A1:B16,2,false) SignatureKind regards, Niek Otten Microsoft MVP - Excel | Using Excel 2002: SP2 | [quoted text clipped - 17 lines] | | gtb > Make a lookup table like this > [quoted text clipped - 45 lines] > | > | gtb Wow folks, two good answers in no time. I went back to my task at hand not expecting any quick response on Saturday a.m. (GMT-5 here). One answer compact (single line) and one more explicit (tabular) but perhaps saves a bit of memory. Either is great if I pay attention to my drag/copy. Thank you both! gtb If you know C/C++ you'll find the syntax of Visual Basic for Applications to be very easy. The "hard" part about VBA is learning the properties of objects. But that comes with time. I've heard from a source, who is a MS MVP in Access and an author of books about Access and SQL, that Office 2007 is the last release of Office that will use VBA. My source thinks that future releases of Office will use Visual Basic .Net. I'm wondering since Visual Basic.Net generates CIL (Common Intermediate Language) as do all of the .NET languages (C++, C#, J# etc) if programming for Office might allow the use of all of the .Net languages, in which case, you would be able to use Visual C++ .Net. Tyro > Using Excel 2002: SP2 > [quoted text clipped - 17 lines] > > gtb I think your source is talking through his --- (fill in the blanks) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > If you know C/C++ you'll find the syntax of Visual Basic for Applications > to be very easy. The "hard" part about VBA is learning the properties of [quoted text clipped - 30 lines] >> >> gtb I don't think so. He's very in with MS. Tyro >I think your source is talking through his --- (fill in the blanks) > [quoted text clipped - 32 lines] >>> >>> gtb Hi Tyro, Read this: http://blogs.msdn.com/excel/archive/2008/01/16/clarification-on-vba-support.aspx SignatureKind regards, Niek Otten Microsoft MVP - Excel |I don't think so. He's very in with MS. | [quoted text clipped - 36 lines] | >>> | >>> gtb Niek: Thanks. I read that. My source told me his info 2 years ago. It makes sense to me. One less language for MS to support and why not use .NET languages? All they need is the JIT (Just In Time Compiler) and the CLR (Common Language Runtime). Best regards, Tyro > Hi Tyro, > [quoted text clipped - 56 lines] > | >>> > | >>> gtb Well I know LOTS of people who are very well in with MS too ... MS would love to ditch VBA, and I bet they even considered it for 2007, but it would be suicide. It would make no difference to all of the new users of course, but there are millions (and I exaggerate not) of applications out there that use and depend upon VBA. If MS were to just drop VBA in the next release, those people will not consider it viable to switch, .Net is not a simple switchover. MS will drop VBA, undoubtedly, at some point, but before they do they would be wise to put a proper roadmap in place, including some sensible and useful migration and deployment tools. Of course, they won't be taking VBA anywhere further, but that is a completely different paradigm. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Niek: > [quoted text clipped - 67 lines] >> | >>> >> | >>> gtb To me it makes perfect sense to get rid of VBA, but of course it has to transition easily to .Net. I would prefer to program in C++ or C# than in Basic. Tyro > Well I know LOTS of people who are very well in with MS too ... > [quoted text clipped - 85 lines] >>> | >>> >>> | >>> gtb That is because that is where you come from. The reason that Excel is the world's most used development platform is because of the way that business users have been able to take Excel, bolt on some VBA, and built some amazing business solutions. They would not be able to do that with C# now, and never with C++ because of the learning curve. So next release of Office ... sorry but I just don't see it happening. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > To me it makes perfect sense to get rid of VBA, but of course it has to > transition easily to .Net. I would prefer to program in C++ or C# than in [quoted text clipped - 91 lines] >>>> | >>> >>>> | >>> gtb They could still program in Visual Basic. Net. Tyro > That is because that is where you come from. The reason that Excel is the > world's most used development platform is because of the way that business [quoted text clipped - 98 lines] >>>>> | >>> >>>>> | >>> gtb VB.NET is not backward compatible with VBA6 so any existing code would have to undergo a major rewrite; that is, once you have learned VB.NET (it is not really an upgrade of VBA6; rather, it is a new language with some keywords that are the same, so there is a learning curve). If Microsoft eliminated VBA in favor of VB.NET (or some other language) in a new version of Excel, then the literally millions of existing VBA code procedures would prevent businesses from moving to up to it (the time required to modify the existing code to work with the VB.NET just to maintain existing functionality would preclude this). The compiled VB world had something similar happen. Microsoft dumped VB6 in favor of VB.NET. Prior to that, estimate were that there were 6 million plus users of VB; today, some 5 or 6 years later, the estimated number of VB.NET users I have seen is about one-third that. Those that did not stay with VB6 moved to other languages (one of which was C#, so those stayed within the .NET family). My guess is that if Excel is serving them well now, a business would simply reject upgrading to a new Excel that did not support their existing base of VBA code. Since Microsoft make huge money off of Office upgrades, I just don't see them shooting themselves in the foot over this. Rick > They could still program in Visual Basic. Net. > [quoted text clipped - 103 lines] >>>>>> | >>> >>>>>> | >>> gtb Exactly. The other point is that even if there were an exact clone of VB in .Net, which there isn't, the development paradigm is dramatically different. Your average business exec who is sitting in his office, has a deadline to produce some figures to show his areas performance, is not going to have the time to learn Visual Studio, PIA, managed code and all of the other stuff that goes with .Net. Where he could immediately dive into VBA and start produce those figures, he will now need a dedicated IT guy, and it just won't happen. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > VB.NET is not backward compatible with VBA6 so any existing code would > have to undergo a major rewrite; that is, once you have learned VB.NET (it [quoted text clipped - 126 lines] >>>>>>> | >>> >>>>>>> | >>> gtb This is where Microsoft blew it with their compiled VB... VB (and VBA by extension), the classic versions of VB if you will, was designed to be accessible by anyone, whether they had almost no programming knowledge or whether they were an expert coder... there is something usable by anyone within the classic version of VB. VB.NET, on the other hand, was designed by programmers for programmers and, as such, is not very approachable by the average user. What Microsoft didn't take into account is the sheer number of non-professionals who were using VB prior to VB.NET; hence, the sharp decline in the user base with its introduction. The VB compiler was never really a big money maker for Microsoft, so I don't think killing off the classic version of VB hurt their bottom line dramatically (at least they think, or thought, that any loss would be made up over the years with the adoption, slow that it might be, of VB.NET). Office and its upgrades, on the other hand, is a cash cow for them and I seriously doubt they will do anything to affect its continuing as such. Rick > Exactly. > [quoted text clipped - 138 lines] >>>>>>>> | >>> >>>>>>>> | >>> gtb
|
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.