Formula - If then else

  • Thread starter Thread starter goodTweetieBird
  • Start date Start date
G

goodTweetieBird

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"})

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|
| 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
 
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)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|
| 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
Click to expand...


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
 
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"})
 
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"}))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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"})

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Your fault? Nay, not when I think of how far along I would be all by
my lonesome.

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
 
I think your source is talking through his --- (fill in the blanks)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
I don't think so. He's very in with MS.

Tyro

Bob Phillips said:
I think your source is talking through his --- (fill in the blanks)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)
Click to expand...
 
Hi Tyro,

Read this:

http://blogs.msdn.com/excel/archive/2008/01/16/clarification-on-vba-support.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I don't think so. He's very in with MS.
|
| Tyro
|
| | >I think your source is talking through his --- (fill in the blanks)
| >
| > --
| > ---
| > 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
| >> 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
| >>>
| >>> 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
| >>
| >>
| >
| >
|
|
 
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
 
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.

--
---
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
Basic.

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

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

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top