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


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([LVL@row = 1, [Accredited Individuals - Business]@row " "), "of 2"))

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



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!