Switch function

K

kramer.newsreader

Hi. I need to use something like a switch function.

I thought it was just "Switch" as it says here (and a couple of other
websites):

http://www.techonthenet.com/excel/formulas/switch.php

but when I try this:

=switch(J4=F2,1,J4=G2,2,3)

I get a name error. I'm using Excel 2003 SP2.

I don't want to use VLookup because I want the value that the function
returns to be from an evaluated formula not from a table and I'm using
references from multiple worksheets.
 
B

Bob Phillips

Maybe this is what you want

=IF(J4=F2,1,IF(J4=G2,2,3))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
K

kramer.newsreader

Bob said:
Maybe this is what you want

=IF(J4=F2,1,IF(J4=G2,2,3))
Click to expand...


I tried that, but it really sucks beyond two cases. Did you know that
you can only nest formulas 7 (or is it 8) levels deep in excel making
nested ifs even worse.
 
B

Biff

Did you miss this line in the article:

"The Switch function can only be used in VBA code."

Biff
 
K

kramer.newsreader

I guess that I did.

Listen up Microsoft people: this function (or a similarly capable one)
should be available in worksheet functions.

I've had several occasions where I needed it.

Is there a way that I can write a VBA function and make it available as
a worksheet function?

if( ,,if(,,if(,,if(,,... really is awful.
 
B

Bob Phillips

Of course I do.

=IF(J4=F2,1,"")&IF(J4=G2,2,"")&...&IF(J4=final_cell,final_num,"")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
B

Biff

if( ,,if(,,if(,,if(,,... really is awful.

Sometimes! But there are several ways to avoid if( ,,if(,,if(,,if(,,...

If you can explain in detail what you're trying to do we'll have a better
chance of coming up with an alternative.

Biff
 
K

kramer.newsreader

I have a worksheet--call it sheet one--which is caclculating a certain
attribute for an entity--one entity per line.

Another worksheet--call it sheet 2--holds the parameters to that
function again one entity per line. One of the parameter is the entity
type, type 1, type 2, type 3, etc.

The entity type determines the form of the function for example:
for entities of type 1, the function might be '=param1*param2 + param3'
whereas for entities of type 2, the function might be '=param1*param2'
whereas for entities of type 3 the function might be '=param3+param2',
etc.
 
B

Biff

=CHOOSE(MATCH(TYPE,{Type1","Type2","Type3},0),param1*param2 +
param3,param1*param2,param3+param2)

As you can see that MIGHT not appear to be any better than
IF(...IF(...IF( but it is! You can have up to 29 conditions versus 7 nested
functions (8 counting the top level).

Biff
 
Top