Multiple IF Statements for Various Scenarios

Tony Fronza

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

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?