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

Executive Alchemist

 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!

Leibel S

@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

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", "")))

Leibel S

@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", "")))

Executive Alchemist

That worked! Thank you so very much!!

Executive Alchemist

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

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

Send screenshot

Executive Alchemist

Thanks for your help!

Leibel S

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", "")))

Executive Alchemist

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