Formula adjustment to add percentages of completion

Miss_Priss

Hello!

Currently I am building a formula to identify, based on a Partner's current tier, how close a Partner is to achieving the next tier by obtaining the total number of certifications needed to advance.

Example: Partner X is currently "Registered" tier which is Level (LVL) 1. In order to advance to the next tier they must achieve 2 certifications in each of the 4 categories. Each certification category is equal to 25%, so achieving 2 certifications in each would represent 100% in this formula cell.

Previously this formula was only identifying achievement for a single tier (Registered) & it was working well. Now when I try to add the current Level (LVL) into the mix, I've broken the formula. Once I've managed to solve this issue, I will be able to replicate it out over the other 4 tiers.

What do I need to change in order for this formula to verify the tier prior to adding up the certification progress? Below is an example of my broken formula:

=IF(AND(LVL@row = 1, [Technical Certified Individuals]@row >= 2, 0.25, 0) + IF(AND(LVL@row = 1, [Foundational Certified Individuals]@row >= 2, 0.25, 0) + IF(AND(LVL@row = 1, [Accredited Individuals - Technical]@row >= 2, 0.25, 0) + IF(AND(LVL@row = 1, [Accredited Individuals - Business]@row >= 2, 0.25, 0)))))

Michael Culley

You need to close off your AND statement with a ).

For example:

=IF(AND(LVL@row = 1, [Technical Certified Individuals]@row >= 2), 0.25, 0) + IF(AND(LVL@row = 1, [Foundational Certified Individuals]@row >= 2), 0.25, 0)

Michael Culley

Since you're just adding a bunch of IF statements, the formula won't end with ))))).

Each formula ends with just a single )

Just make sure you add the ) after every 2 right before the comma.

Miss_Priss

@Michael Culley Thanks for the input!

I've made the recommended updates but I am still receiving the Unparseable error. Below is the updated formula. Any ideas where I went wrong?

=IF(AND(LVL@row = 1, [Technical Certified Individuals]@row >= 2,) 0.25, 0) + IF(AND(LVL@row = 1, [Foundational Certified Individuals]@row >= 2), 0.25, 0) + IF(AND(LVL@row = 1, [Accredited Individuals - Technical]@row >= 2), 0.25, 0) + IF(AND(LVL@row = 1, [Accredited Individuals - Business]@row >= 2), 0.25, 0)

Michael Culley

@Miss_Priss

You put the ) in the wrong spot in the beginning. It goes before the comma.

You put =IF(AND(LVL@row = 1, [Technical Certified Individuals]@row >= 2,) 0.25, 0)

Should be =IF(AND(LVL@row = 1, [Technical Certified Individuals]@row >= 2), 0.25, 0)

Miss_Priss

@Michael Culley Thanks for catching that! That will teach me to multitask, lol.

While adding the other levels & certification thresholds, it's caused another Unparseable error. In order to expand on this formula, what am I missing to add the additional arguments? Example below with levels 2-4 added.

=IF(AND(LVL@row = 1, [Technical Certified Individuals]@row >= 2), 0.25, 0) + IF(AND(LVL@row = 1, [Foundational Certified Individuals]@row >= 2), 0.25, 0) + IF(AND(LVL@row = 1, [Accredited Individuals - Technical]@row >= 2), 0.25, 0) + IF(AND(LVL@row = 1, [Accredited Individuals - Business]@row >= 2), 0.25, 0), IF(AND(LVL@row = 2, [Technical Certified Individuals]@row >= 6), 0.25, 0) + IF(AND(LVL@row = 2, [Foundational Certified Individuals]@row >= 4), 0.25, 0) + IF(AND(LVL@row = 2, [Accredited Individuals - Technical]@row >= 4), 0.25, 0) + IF(AND(LVL@row = 2, [Accredited Individuals - Business]@row >= 4), 0.25, 0), IF(AND(LVL@row = 3, [Technical Certified Individuals]@row >= 25), 0.25, 0) + IF(AND(LVL@row = 3, [Foundational Certified Individuals]@row >= 10), 0.25, 0) + IF(AND(LVL@row = 3, [Accredited Individuals - Technical]@row >= 10), 0.25, 0) + IF(AND(LVL@row = 3, [Accredited Individuals - Business]@row >= 10), 0.25, 0), IF(AND(LVL@row = 4, [Technical Certified Individuals]@row >= 25), 0.25, 0) + IF(AND(LVL@row = 4, [Foundational Certified Individuals]@row >= 10), 0.25, 0) + IF(AND(LVL@row = 4, [Accredited Individuals - Technical]@row >= 10), 0.25, 0) + IF(AND(LVL@row = 4, [Accredited Individuals - Business]@row >= 10), 0.25, 0)

Michael Culley

@Miss_Priss

You're forgetting to put a + in front of some of your IFs.

And yea I would say when working on formulas that are this long I wouldn't recommend multitasking at all lol.

Michael Culley

You are instead putting commas. Treat each of those If/AND statements as if they are their own sentence. They end with a parenthesis followed by a +

Miss_Priss

@Michael Culley You're amazing! I was wondering if I should use the + instead of a comma, but my thought was that I didn't want it to be a continuation of the math problem, so it didn't make sense in my head, lol. Still learning how to transition from an excel mindset to Smartsheet.

Thank you for being my superhero for the day! 😁

Michael Culley

No problem. Yea I'm not sure what result you're trying to get so I assumed you wanted to add all those together. If you aren't getting the result you're looking for let me know and we'll go for there. For now, I'm glad it helped you lol.