Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: More than 7 \

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Department of Human Services
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Excel limits the number of "IFs" that can be group in one formula to seven. Is there any way around this limitation. I would like to be able to have 8 "IFs" in one formula.

    The formula containing the 7 "IFs" is listed below:
    =IF(N200="lam/cont",VLOOKUP(N201,pricelist,2,TRUE),IF(N200="vinyl 1",VLOOKUP(N201,pricelist,3,TRUE),IF(N200="vinyl 2",VLOOKUP(N201,pricelist,4,TRUE),IF(N201="vinyl 3",VLOOKUP(N201,pricelist,5,TRUE),IF(N200="vinyl 4",VLOOKUP(N201,pricelist,6,TRUE),IF(N200="tas oak",VLOOKUP(N201,pricelist,7,TRUE),IF(N200="blackwood",VLOOKUP(N201,pricelist,8,TRUE),"")))))))

    _________________
    Phil

    Phil.Jackel@dhs.vic.gov.au

    [ This Message was edited by: phil j on 2002-04-22 19:53 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-22 18:53, Phil J wrote:
    Excel limits the number of "IFs" that can be group in one formula to seven. Is there any way around this limitation. I would like to be able to have 8 "IFs" in one formula.
    Create your own function in VBA.

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Phil,

    Many ways around this limit. Post your request in more detail and the appropriate solution will be found.

    If you'd like to take a crack at it before posting again, the main options are:

    1. IFs separated within the formula
    2. Boolean algebra
    3. Lookup tables (VLOOKUP,HLOOKUP,LOOKUP,INDEX/MATCH)
    4. Rewriting the formula

    Bye,
    Jay

  4. #4
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Pls Try this.

    =VLOOKUP(N201,pricelist,MATCH(N200,{"lam/cont","vinyl 1","vinyl 2","vinyl 3","vinyl 4","tas oak","blackwood"},0)+1,TRUE)

    [ This Message was edited by: Colo on 2002-04-22 21:17 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,006
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-22 18:53, Phil J wrote:
    Excel limits the number of "IFs" that can be group in one formula to seven. Is there any way around this limitation. I would like to be able to have 8 "IFs" in one formula.

    The formula containing the 7 "IFs" is listed below:
    =IF(N200="lam/cont",VLOOKUP(N201,pricelist,2,TRUE),IF(N200="vinyl 1",VLOOKUP(N201,pricelist,3,TRUE),IF(N200="vinyl 2",VLOOKUP(N201,pricelist,4,TRUE),IF(N201="vinyl 3",VLOOKUP(N201,pricelist,5,TRUE),IF(N200="vinyl 4",VLOOKUP(N201,pricelist,6,TRUE),IF(N200="tas oak",VLOOKUP(N201,pricelist,7,TRUE),IF(N200="blackwood",VLOOKUP(N201,pricelist,8,TRUE),"")))))))

    _________________
    Phil

    Phil.Jackel@dhs.vic.gov.au

    [ This Message was edited by: phil j on 2002-04-22 19:53 ]
    Make a 2-column table, consisting of

    {"lam/cont",2;
    "vinyl 1",3;
    "vinyl 2",4;
    "vinyl 3",5;
    "vinyl 4",6;
    "tas oak",7;
    "blackwood",8}

    and name it ColList via the name Box.

    Change your VLOOKUP formula to:

    =VLOOKUP(N201,pricelist,VLOOKUP(N200,ColList,2,0))

    Alternatively, make a 1-column list:

    {"lam/cont";
    "vinyl 1";
    "vinyl 2";
    "vinyl 3";
    "vinyl 4";
    "tas oak";
    "blackwood"}

    and name it Categories.

    Change the VLOOKUP formula to:

    =VLOOKUP(N201,pricelist,MATCH(N200,Categories,0)+1)

    You can expand ColList or Categories to cover more as needed.

    Aladin



    [ This Message was edited by: Aladin Akyurek on 2002-04-22 21:39 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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