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 "(ARC 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

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) + "(ARC 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", "")))

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 (ARC 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

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) + "(ARC 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!!

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) + "(ARC 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!

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

Thanks for your help!

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 (ARC 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
Categories
Check out the Formula Handbook template!