Multiple IF Statements for Various Scenarios

Hello! Based on multiple criteria, I'm building a formula to yield a completion %:

I want the results to yield 0% if a specific field is marked "No" or another field is marked "No" or "N/A", populate 50% (.5) if one of two criteria are met, and populate 100% (1) if all criteria are met.

I started building the formula below:

=IF([LTC Target]@row = "No", 0), IF(OR([LTC Intro Meeting Completed]@row = "N/A", [LTC Intro Meeting Completed]@row = "No"), 0), IF(AND([LTC Intro Meeting Completed]@row = "Yes", [LTC Quote / Proposal Issued]@row = "Yes") 1)

I've also attached the sheet I'm working with for reference. Any help is much appreciated!

Master Client Tracking Sheet for ABS.xlsx

Find more posts tagged with

Formulas

Accepted answers

All comments

Kleerfyre

In a nested IF formula, you don't put the ) at the end of each IF statement, just a , and then at the very end, put the needed ) for each of the IF statements.

Nic Larsen

Try this: =IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "Yes"), 1, IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "No"), 0.5, IF(AND(OR([LTC Intro Meeting Complete]@row = "No", [LTC Intro Meeting Complete]@row = "N/A"), [LTC Quote / Proposal Issued]@row = "Yes"), 0.5, IF(AND([LTC Target]@row = "No", [LTC Intro Meeting Complete]@row = "No"), 0, 0))))

Tony Fronza

@Nic Larsen thanks for jumping in on this! I tried your formula, but I'm still receiving a UNPARSEABLE error. Thoughts?

Kleerfyre

Its going to be the AND statements and the OR statements where your issue is coming from. Let me look at these two formulas again and I will figure it out. I have some similar formulas that might be able to be adjusted to fit your use case.

Hollie Green

@Tony Fronza have you tried breakout out the formula into separate parts to see where the error is? example

=IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "Yes"), 1,0) and see if it works appropriately then

IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "No"), 0.5,0) until you check all the If statements individually. It's usually a comma or a ) out of place that's the easiest way I have found to discover errors in long formulas.

Kleerfyre

I don't think this formula will work, but you can try it. It is Nick's with an adjustment.

=IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "Yes"), 1, IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "No"), 0.5, IF(AND(OR([LTC Intro Meeting Complete]@row = "No", [LTC Intro Meeting Complete]@row = "N/A"), [LTC Quote / Proposal Issued]@row = "Yes"), 0.5, IF(AND([LTC Target]@row = "No", [LTC Intro Meeting Complete]@row = "No"), 0))))

I think having the AND(OR is causing the issue in this formula though.

Edit:

Here is another formula for you to try.

=IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "Yes"), 1, IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "No"), 0.5, IF(AND([LTC Intro Meeting Complete]@row = "No", [LTC Intro Meeting Complete]@row = "N/A"), 0.5, IF(AND([LTC Intro Meeting Complete]@row = "No", [LTC Quote / Proposal Issued]@row = "Yes"), 0.5, IF(AND([LTC Target]@row = "No", [LTC Intro Meeting Complete]@row = "No"), 0)))))

Tony Fronza

While thinking through this, I want the formula to reflect a % if the field is NOT blank (much easier logic) for three fields (i.e., 33% for the first field, 33% for the second field, and 34% for the third to = 100). Could someone help me write this formula?

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.