+ Reply to Thread
Results 1 to 10 of 10

Switch function

  1. #1

    Switch function

    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.


  2. #2

    Re: Switch function

    Correction
    [email protected] wrote:
    > 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)

    =switch(J4=F2,1,J4=G2,2,J4=H2,3)
    actually gives the name error (although the above will give a name
    error as well)

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



  3. #3
    Bob Phillips
    Guest

    Re: Switch function

    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)

    <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >




  4. #4

    Re: Switch function


    Bob Phillips wrote:
    > Maybe this is what you want
    >
    > =IF(J4=F2,1,IF(J4=G2,2,3))



    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.


  5. #5
    Biff
    Guest

    Re: Switch function

    Did you miss this line in the article:

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

    Biff

    <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >




  6. #6

    Re: Switch function

    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.


  7. #7
    Bob Phillips
    Guest

    Re: Switch function

    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)

    <[email protected]> wrote in message
    news:[email protected]...
    >
    > Bob Phillips wrote:
    > > Maybe this is what you want
    > >
    > > =IF(J4=F2,1,IF(J4=G2,2,3))

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




  8. #8
    Biff
    Guest

    Re: Switch function

    >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

    <[email protected]> wrote in message
    news:[email protected]...
    >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.
    >




  9. #9

    Re: Switch function

    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.

    Biff wrote:
    > >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
    >
    > <[email protected]> wrote in message
    > news:[email protected]...
    > >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.
    > >



  10. #10
    Biff
    Guest

    Re: Switch function

    =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

    <[email protected]> wrote in message
    news:[email protected]...
    >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.
    >
    > Biff wrote:
    >> >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
    >>
    >> <[email protected]> wrote in message
    >> news:[email protected]...
    >> >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.
    >> >

    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1