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!

Find more posts tagged with

Accepted answers

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

All comments

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:

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.