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
-
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
-
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
-
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
-
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", "")))))
-
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
-
Here is the formula I put in and still not working:
-
Still not working. Any other suggestions?
-
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!
-
Thank you Paul but still not working...
-
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!
-
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
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!