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 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!

«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    In that case you would use something like this:

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

  • Miss_Priss
    Miss_Priss ✭✭
    edited 04/26/23
  • @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(LVL@row = 1, "2", IF(LVL@row = 2, "4", IF(LVL@row = 3, "10", IF(LVL@row = 4, "10", IF(LVL@row = 5, " ")))))))

  • Andrée Starå
    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(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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    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(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"))))))))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!