In search of a formula to combine cell value and predictive qualifications
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 14 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!
Answers


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

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"))
thinkspi.com

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

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

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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

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, "")))))
thinkspi.com

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

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, ""))))))
thinkspi.com
Help Article Resources
Categories
Check out the Formula Handbook template!