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([LVL@row = 1, [Accredited Individuals  Business]@row " "), "of 2"))
Hopefully this explanation makes sense. Please ask any clarifying questions!
Answers

Are you able to provide a screenshot for context?

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(LVL@row = 1, "2", IF(LVL@row = 2, "4"))

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(LVL@row = 1, "2", IF(LVL@row = 2, "4", IF(LVL@row = 3, "10", IF(LVL@row = 4, "10", IF(LVL@row = 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(LVL@row = 1, "2", IF(LVL@row = 2, "4", IF(LVL@row = 3, "10", IF(LVL@row = 4, "10", IF(LVL@row = 5, ""))))))
Or this. (My personal preference)
=IF([Accredited Individuals  Business]@row <> "", [Accredited Individuals  Business]@row + " of " + IF(LVL@row = 1, "2", IF(LVL@row = 2, "4", IF(LVL@row = 3, "10", IF(LVL@row = 4, "10", IF(LVL@row = 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 for help with Smartsheet, integrations, general workflow advice, or anything else.

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(LVL@row = 1, "2", IF(LVL@row = 2, "4", IF(LVL@row = 3, "10", IF(LVL@row = 4, "10", IF(LVL@row = 5, "")))))

@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(LVL@row = 1, [Accredited Individuals  Business]@row > =0), "", IF(AND([Accredited Individuals  Business]@row + " of " + IF(AND(LVL@row = 2, "2", IF(AND(LVL@row = 3, "4", IF(AND(LVL@row = 4, "4", IF(AND(LVL@row = 5, "10"))))))))))

Try something like this:
=IF(AND(LVL@row>1, [Accredited Individuals  Business]@row <> ""), [Accredited Individuals  Business]@row + " of " + IF(LVL@row = 1, "2", IF(LVL@row = 2, "4", IF(LVL@row = 3, "10", IF(LVL@row = 4, "10", IF(LVL@row = 5, ""))))))

@Paul Newcome That did something but not quite what I had in mind. It looks like it wiped out the data for any LVL 1, which is not what I expected when I read the formula.
I tried to make some changes but I guess I still can't get it quite right.
There's a few things I want as a set response in different scenarios for this formula.
1) If the LVL is 1 then I only want to know what is in the Accredited Individuals  Business cell rather than comparing it to the base requirements since none exist for LVL 1.
2) If the Accredited Individuals  Business cell is blank, then I want a blank response.
3) If the Accredited Individuals  Business cell value is 0 then I want a response of ".."
The formula revision below is giving me a mix of wrong responses and #INVALID DATA.
=IF(AND(LVL@row = 1, [Accredited Individuals  Business]@row), IF([Accredited Individuals  Business]@row = 0, ".."), IF([Accredited Individuals  Business]@row <> ""), [Accredited Individuals  Business]@row + " of " + IF(LVL@row = 2, "2", IF(LVL@row = 3, "4", IF(LVL@row = 4, "4", IF(LVL@row = 5, "10")))))
Any thoughts on this?

@Andrée Starå Can you please look at the above? I feel like my formula is close based on the outcome I'm looking for but I just can't quite figure out what's missing.

My apologies for missing your last response. Try this:
=IF(LVL@row = 1, [Accredited Individuals  Business]@row, IF([Accredited Individuals  Business]@row = 0, "..", IF([Accredited Individuals  Business]@row <> "", [Accredited Individuals  Business]@row + " of " + IF(LVL@row = 2, "2", IF(LVL@row = 3, "4", IF(LVL@row = 4, "4", IF(LVL@row = 5, "10")))))))
It looks like you just had some misplaced parenthesis.

@Paul Newcome Thanks for the response!
This formula solved almost everything but for some reason it's not recognizing the zeros from this part of the formula IF([Accredited Individuals  Business]@row = 0, ".."
I've tried to move some things around to fix it but I'm not sure where the problem is. Everything else works great.

You may have answered this already and I just need more coffee, but... How exactly are you populating the [Accredited Individuals  Business] column?

@Paul Newcome The [Accredited Individuals  Business] column cells are linked to another sheet which is updated weekly. The cell value ranges from a blank cell, and anywhere from 0 up.
Let me know if I misunderstood your question.
Help Article Resources
Categories
Check out the Formula Handbook template!