Unparseable Multiple IF functions within one equation

Hi, I am trying to put multiple IF functions in one equation and it doesn't seem to be working.

The formula should return differently if:

1) the program type is "Active"

2) the program is a virtual program and not a Youth & Adult Program

3) the program is a virtual program and is a Youth & Adult Program

4) Otherwise (The program is not a Virtual Program, Not a Youth and Adult Program and not "Active")

=IF([U12 In-Person Program Type]@row = "Active", ([Facility Name]@row+" "+"Active/Multisport" + " " +[Program Day of Week]@row +" " + [Season]@row),(IF(AND([Virtual]@row=true, [Youth & Adult Program]@row=false),[Virtual Program Type]@row + " Virtual Program" + [Program Day of Week]@row + " " + [Season]@row),(IF(AND([Virtual]@row=true,[Youth & Adult Program]@row=true),"N/A",([Facility Name]@row + " " + [U12 In-Person Program Type]@row + " " + [Program Day of Week]@row +" " + [Season]@row)))))


Are my parantheses in the right place? Any insight into what the issue could be would be appreciated.

Answers

  • Mary_A
    Mary_A ✭✭✭✭✭✭

    This is a debug problem. Create "Helper Columns" to simplify your function. Each helper column would contain one of your long row concatenations, for example:

    Helper_ActiveDesc the column would be a text column containing ([Facility Name]@row+" "+"Active/Multisport" + " " +[Program Day of Week]@row +" " + [Season]@row)

    Helper columns. can make your function more readable which will help you debug it.

    IF([U12 In-Person Program Type]@row = "Active", [Helper_ActiveDesc]@row,IF(...

    The helper columns also ensure your syntax on the long concatenates is correct. And if you want to change a description in the future, it will be easier. Just put the helper columns at the end of the table and hide them.

    Once you have made the function readable. Break it apart and verify each IF works as is BEFORE you nest them.

  • Thanks Mary!

    That is a great tip for next time. I also got it to work without using helper columns. Just posting my solution here, in case the format of it might help someone else:

    =IF(NOT([U12 In-Person Program Type]@row = "Active"), (IF([Youth & Adult Program]@row = false, (IF(Virtual@row = true, [Virtual Program Type]@row + " Virtual Program " + [Program Day of Week]@row + " " + Season@row, [Facility Name]@row + " " + [U12 In-Person Program Type]@row + " " + [Program Day of Week]@row + " " + Season@row)), "N/A")), [Facility Name]@row + " " + "Active/Multisport" + " " + [Program Day of Week]@row + " " + Season@row)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!