In search of a formula to combine cell value and predictive qualifications

Miss_Priss

Hello!

After racking my brain I'm just not sure this formula is possible. I have a workaround but it would be great if anyone could help me crack this mystery!

The goal of this formula would be for it to

1) Read the numerical value of the "Level" (LVL) cell in order to determine the requirements

2) Include the numerical value of the "Certification" (Accredited Individuals - Business) cell as part of the cell response

3) Predict based on the Level what the goal would be (i.e. "of 2")

If the Level were 1, then the Certification goal would be 2. However, if the existing Certification value is 1 then the ideal response for this formula would be "1 of 2". The value of 1 being supplied by the Certification cell, then the prediction of 2 as the goal would be driven by the formula based on reading the Level cell. Levels 1-4 have varying certification goals , which would result in this formula being rather lengthy but it would be a breeze if I have a baseline to build from.

So far this is what I've tried: =IF(AND([[email protected] = 1, [Accredited Individuals - Business]@row " "), "of 2"))

Hopefully this explanation makes sense. Please ask any clarifying questions!

Paul Newcome

Are you able to provide a screenshot for context?

Miss_Priss

https://community.smartsheet.com/discussion/comment/373165#Comment_373165

Sure, the snippet is now included for reference. The test column is where I've been working through the formula.

Each LVL or Level has it's own requirements in order to progress to the next level. Ideally the formula I create will read as, if you are Level 1 for example you must have 2 of these Certifications (Accredited Individuals - Business) in order to progress to the next level.

The second line in this image is Level 2 with 2 certifications in this category. For this example, a Level 2 would require 4 certifications of this category in order to progress to Level 3. The formula I would like to use would result in a response statement that would say what the current certification value is (2) and what is needed (4) based on the current Level (2). The formula statement in the example of the second line would say, 2 of 4

Paul Newcome

In that case you would use something like this:

=[Accredited Individuals - Business]@row + " of " + IF([email protected] = 1, "2", IF([email protected]row = 2, "4"))

Miss_Priss

https://community.smartsheet.com/discussion/comment/373207#Comment_373207

That's magic! Thank you so much for cracking the case! 😁

Miss_Priss

@Paul Newcome -

Any idea how to add the IF(ISBLANK to the beginning of this formula to result in a blank response if the certification cell ([Accredited Individuals - Business]@row) is blank?

I've been messing around with this but I think I'm backed into a corner. I must be missing an element to make this a true statement. (IF(AND(ISBLANK?)

=IF(ISBLANK([Accredited Individuals - Business]@row, " ", [Accredited Individuals - Business]@row + " of " + IF([email protected] = 1, "2", IF([email protected] = 2, "4", IF([email protected] = 3, "10", IF([email protected] = 4, "10", IF([email protected] = 5, " ")))))))

Andrée Starå

Hi @Miss_Priss

I hope you're well and safe!

Try something like this.

=IF(ISBLANK([Accredited Individuals - Business]@row), "", [Accredited Individuals - Business]@row + " of " + IF([email protected] = 1, "2", IF([email protected] = 2, "4", IF([email protected] = 3, "10", IF([email protected] = 4, "10", IF([email protected] = 5, ""))))))

Or this. (My personal preference)

=IF([Accredited Individuals - Business]@row <> "", [Accredited Individuals - Business]@row + " of " + IF([email protected] = 1, "2", IF([email protected] = 2, "4", IF([email protected] = 3, "10", IF([email protected] = 4, "10", IF([email protected] = 5, ""))))))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Happy to help. 👍️

I would use this to leave it blank if that cell is blank:

IF([Accredited Individuals - Business]@row <> "", [Accredited Individuals - Business]@row + " of " + IF([email protected] = 1, "2", IF([email protected] = 2, "4", IF([email protected] = 3, "10", IF([email protected] = 4, "10", IF([email protected] = 5, "")))))

Miss_Priss

@Paul Newcome & @Andrée Starå - Thank you so much for your help! This worked like a dream.

Now that I am taking this to the next level I've run into another challenge creating a comprehensive formula to get the result I'm looking for.

The goal of this new column would be the calculate the compliance of the current level. There are requirements that must be met in order to be considered in compliance and stay at an existing level. However, Level (LVL) 1 does not have any base requirements. So rather than showing their current status (which in most cases is 0) and combining it with "of" + the base requirement which doesn't exist for Level 1, how can I set up a formula to get a response of just the current Accredited Individuals - Business cell?

I feel like I'm not far off with the formula below but I could be wrong. Any thoughts on this variation?

Also, what are the chances of being able to combine this with the blank cell formula?

=IF(AND([email protected] = 1, [Accredited Individuals - Business]@row > =0), "", IF(AND([Accredited Individuals - Business]@row + " of " + IF(AND([email protected] = 2, "2", IF(AND([email protected] = 3, "4", IF(AND([email protected] = 4, "4", IF(AND([email protected] = 5, "10"))))))))))

Paul Newcome

Try something like this:

=IF(AND([email protected]>1, [Accredited Individuals - Business]@row <> ""),  [Accredited Individuals - Business]@row + " of " + IF([email protected] = 1, "2", IF([email protected] = 2, "4", IF([email protected] = 3, "10", IF([email protected] = 4, "10", IF([email protected] = 5, ""))))))