Trying to get specific text to show in a column if a value in cell is less than 4

Here is my scenario:


You

I have 6 columns Sheet. I want the name of the first column where the value of a cell is less than 4, to populate a 6th column. In other words, dealing with one row at a time, if my column names are Awareness, Desire, Knowledge, Ability, Reinforcement, and Barrier Point, I want the name of the column of the first 5 columns (Awareness, Desire, Knowledge, Ability, Reinforcement) where the value is less than 4 to appear in the Barrier Point column.

Here is where I am:

=IF(B2 < 4, "Awareness", IF(C2 < 4, "Desire", IF(D2 < 4, "Knowledge", IF(E2 < 4, "Ability", IF(F2 < 4, "Reinforcement", "")))))


Coming up as #UNPARSEABLE. Help please?

Answers

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭

    Hi @ruthcurran,

    I want to make sure I understand what you want the formula to do. Do you want it to contain the name of all ratings less than four or only if they are less than four in this particular order?

    The reason I ask is that the formula you were working on is not how the description read to me.

    If you will help clarify for me, I will try to help with the formula.

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • ruthcurran
    ruthcurran ✭✭✭

    Thank you! I want it to contain the name of the first rating (the first time a value is less than 4) to appear. I so appreciate your guidance!


    Ruth

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭

    Absolutely! The error message #UNPARSEABLE means that the formula is unable to process due to something within the formula. You do not have any missing quotes or parenthesis.

    Is it possible that one of the column names is incorrect? It wouldn't be a bad idea to toss brackets around your column names just in case. That would look like:

    • =IF([B]2 < 4, "Awareness", IF([C]2 < 4, "Desire", IF([D]2 < 4, "Knowledge", IF([E]2 < 4, "Ability", IF([F]2 < 4, "Reinforcement", "")))))

    If that doesn't help feel free to post a screenshot of your sheet. That may help us determine what the issue might be. Thanks!

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • ruthcurran
    ruthcurran ✭✭✭

    I copied and pasted.... My columns have to be wrong?

    =IF([B]2 < 4, "Awareness", IF([C]2 < 4, "Desire", IF([D]2 < 4, "Knowledge", IF([E]2 < 4, "Ability", IF([F]2 < 4, "Reinforcement", "")))))

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭

    Okay. There we are! Unlike a spreadsheet like Excel or Google Sheet, these columns use the names you give them, not just a letter. So this formula should work if it looks like:

    • =IF([Awareness]@row < 4, "Awareness", IF([Desire]@row < 4, "Desire", IF([Knowledge]@row < 4, "Knowledge", IF([Ability]@row < 4, "Ability", IF([Reinforcement]@row < 4, "Reinforcement", "")))))

    Hope this helps!

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • ruthcurran
    ruthcurran ✭✭✭

    Here is the formula I put in and still not working:



  • ruthcurran
    ruthcurran ✭✭✭

    Still not working. Any other suggestions?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this:

    =IF(Awareness@row< 4, "Awareness" + CHAR(10), "") + IF(Desire@row< 4, "Desire" + CHAR(10), "") + IF(Knowledge@row< 4, "Knowledge" + CHAR(10), "") + IF(Ability@row< 4, "Ability" + CHAR(10), "") + IF(Reinforcement@row< 4, "Reinforcement" + CHAR(10), "")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • ruthcurran
    ruthcurran ✭✭✭

    Thank you Paul but still not working...


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I think I see the problem, We are using Ability@row, but it looks like your column name is spelled incorrectly as Abiiity. Make sure column names match exactly with what is used in the formula.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • ruthcurran
    ruthcurran ✭✭✭
    edited 12/22/23

    Oooooooooooooooooooooooooh! It is always the small things and I am not sure how I missed that! Thank you @Paul Newcome!


    However all selections are showing, not just the first instance when the number is less than 4. How do I get just the first instance to show up?


    Thanks!

    Ruth

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I'm not sure I understand your question.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!