Formula assistance. First 4 parts working, need assist with last 3 parts.

 I am needing assistance with 3 formulas to complete a project. I cant seem to get this one to work in entirety. I can get the first 4 parts to work, however, not the last one shown here in bold and have one more part to add:

=IF([25. Assurance project type for set up]@row = "AUP - Verif", 3, IF([25. Assurance project type for set up]@row = "AUP - Parity", 3, IF([25. Assurance project type for set up]@row = "AUP - Lease Suff", 3, IF([25. Assurance project type for set up]@row = "AUP - Landfill Assistance", 3, IF([25. Assurance project type for set up]@row = "Bond Issue - Comp Forecast/Projection "(AR-C 80)"", 4)))))  

Need to add:  

IF([25. Assurance project type for set up]@row = "CD - Comp GAAP_w/o discl", NA, IF([25. Assurance project type for set up]@row =" CD - Comp Cash basis_w/o discl", NA)))))))

 The other two are a bit more complicated. Any help or guidance is greatly appreciated! TIA!

Best Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Executive Alchemist

    Your original formula has an issue because of the quote marks, you need to replace those with CHAR(34) INSTEAD.

    As well, i simplified your first part of the formula using CONTAINS, and added the items you wanted added:

    =IF(CONTAINS([25. Assurance project type for set up]@row, "AUP - Verif AUP - Parity AUP - Lease Suff AUP - Landfill Assistance"), 3, IF([25. Assurance project type for set up]@row = "Bond Issue - Comp Forecast/Projection " + CHAR(34) + "(AR-C 80)" + CHAR(34), 4, IF(CONTAINS([25. Assurance project type for set up]@row, "CD - Comp GAAP_w/o discl CD - Comp Cash basis_w/o discl"), "NA", "")))

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    So then you dont need the quote marks you originally showed:

    Use below revised:

    =IF(CONTAINS([25. Assurance project type for set up]@row, "AUP - Verif AUP - Parity AUP - Lease Suff AUP - Landfill Assistance"), 3, IF([25. Assurance project type for set up]@row = "Bond Issue - Comp Forecast/Projection (AR-C 80)", 4, IF(CONTAINS([25. Assurance project type for set up]@row, "CD - Comp GAAP_w/o discl CD - Comp Cash basis_w/o discl"), "NA", "")))

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Executive Alchemist

    Your original formula has an issue because of the quote marks, you need to replace those with CHAR(34) INSTEAD.

    As well, i simplified your first part of the formula using CONTAINS, and added the items you wanted added:

    =IF(CONTAINS([25. Assurance project type for set up]@row, "AUP - Verif AUP - Parity AUP - Lease Suff AUP - Landfill Assistance"), 3, IF([25. Assurance project type for set up]@row = "Bond Issue - Comp Forecast/Projection " + CHAR(34) + "(AR-C 80)" + CHAR(34), 4, IF(CONTAINS([25. Assurance project type for set up]@row, "CD - Comp GAAP_w/o discl CD - Comp Cash basis_w/o discl"), "NA", "")))

  • That worked! Thank you so very much!!

  • Executive Alchemist
    edited 12/14/23

    I tried this again today and the following part does not work within the recommended formula from Leibel S: "IF([25. Assurance project type for set up]@row = "Bond Issue - Comp Forecast/Projection " + CHAR(34) + "(AR-C 80)" + CHAR(34), 4," any idea why? Your formula returns the correct "3" or NA rating, however, not the "4". Thank you for any help!

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    What is in the [25. Assurance project type for set up] field?


    Send screenshot

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    So then you dont need the quote marks you originally showed:

    Use below revised:

    =IF(CONTAINS([25. Assurance project type for set up]@row, "AUP - Verif AUP - Parity AUP - Lease Suff AUP - Landfill Assistance"), 3, IF([25. Assurance project type for set up]@row = "Bond Issue - Comp Forecast/Projection (AR-C 80)", 4, IF(CONTAINS([25. Assurance project type for set up]@row, "CD - Comp GAAP_w/o discl CD - Comp Cash basis_w/o discl"), "NA", "")))

  • Perfect! This works! Thank you so much for the help and the simplification lesson! Much appreciated!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!