Forum Discussion
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
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.
=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) .
- THANK YOU!!!!
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
- I really appreciate the help. I am new at this and dont know how to use switch or a look up.
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.
