Forum Discussion

Integrity_Drywall's avatar
Integrity_Drywall
Copper Contributor
Sep 14, 2022

Help on an IFS statement please

I am trying to write an IFS statement to make life easier while using an Excel sheet at work.  I was told there is no limit on the amount of data in the IFS statement but I keep getting an error and I am not sure if I am doing the statement wrong or if it is just to large and I cant have it do what I want it to do.  Basically what I am trying to do is have the square footage column auto populate based on the model name when you enter it.  Can someone please help.  The error is :  

 

Error  Formula parse error

 

 

The statement I am trying to us is:  

=IFS(D32="136 PARROT", 1940, D32="175 CABANA", 2393, D32="175 II CABANA BAY”, 2393, D32="176 BREEZE”,2083, D32="176 II BREEZE BAY”,1616, D32="177 COCONUT”,1685, D32="178 HAMMOCK”,2114, D32="178 II HAMMOCK BAY”,2114, D32="179 TRINIDAD”,2568, D32="179 II TRINIDAD BAY”,2549, D32="181 ST. BART”,2492, D32="184 ARUBA”,2336, D32="169 ANTIGUA”,1565, D32="171 BARBUDA”,1787, D32="171 II BARBUDA BAY”,0, D32="172 LUCIA”,1773, D32="173 NEVIS”,1865, D32="183 JAMAICA”,1507, D32="195 TORTOLA”,1743, D32="189 ALOHA”, 1466, D32="190 BAMBOO”, 1204, D32="191 CAMELLIA”,1321, D32="192 DREAMSICLE”,1378)")

 

 

Any help would be greatly appreciated.

 

Thank you

 

excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Sep 14, 2022

    Integrity_Drywall  In the attached i created a table with your model names and sq.ft. info and then to the right I have a drop down to select a model and then in the next cell I use VLOOKUP to lookup the sq.ft. for the model you selected.  I hope it helps you learn/understand how to use a LOOKUP.  There are also innumerous resources on the web.  I highly recommend this method as it makes your life much easier.  The formula is much smaller and easier to understand (once you understand LOOKUP) and you can easily update/change the information in the table and not have to search every formula. 

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Sep 14, 2022

    Integrity_Drywall 

    =IFS(D32="136 PARROT", 1940, D32="175 CABANA", 2393, D32="175 II CABANA BAY", 2393, D32="176 BREEZE",2083, D32="176 II BREEZE BAY",1616, D32="177 COCONUT",1685, D32="178 HAMMOCK",2114, D32="178 II HAMMOCK BAY",2114, D32="179 TRINIDAD",2568, D32="179 II TRINIDAD BAY",2549, D32="181 ST. BART",2492, D32="184 ARUBA",2336, D32="169 ANTIGUA",1565, D32="171 BARBUDA",1787, D32="171 II BARBUDA BAY",0, D32="172 LUCIA",1773, D32="173 NEVIS",1865, D32="183 JAMAICA",1507, D32="195 TORTOLA",1743, D32="189 ALOHA", 1466, D32="190 BAMBOO", 1204, D32="191 CAMELLIA",1321, D32="192 DREAMSICLE",1378)

     

    It works in my excel online sheet after replacing ” by " and changing the end of the formula 1378)") to 1378) .

  • mtarler's avatar
    mtarler
    Silver Contributor
    Sep 14, 2022

    Integrity_Drywall  first off the error appears to be at the end:

    ...., D32="192 DREAMSICLE”,1378)")

    That final ") doesn't match anything

    That said I highly suggest you create a table with the values you want and use a simple LOOKUP instead of this large IFS statement.  BTW, another option in this case would have been to use SWITCH but a LOOKUP table would still be better IMHO

     

     

    • Integrity_Drywall's avatar
      Integrity_Drywall
      Copper Contributor
      Sep 14, 2022
      I really appreciate the help. I am new at this and dont know how to use switch or a look up.
      • mtarler's avatar
        mtarler
        Silver Contributor
        Sep 14, 2022

        Integrity_Drywall  In the attached i created a table with your model names and sq.ft. info and then to the right I have a drop down to select a model and then in the next cell I use VLOOKUP to lookup the sq.ft. for the model you selected.  I hope it helps you learn/understand how to use a LOOKUP.  There are also innumerous resources on the web.  I highly recommend this method as it makes your life much easier.  The formula is much smaller and easier to understand (once you understand LOOKUP) and you can easily update/change the information in the table and not have to search every formula.