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

ruthcurran

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?

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.

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

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!

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

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!

ruthcurran

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

ruthcurran

Still not working. Any other suggestions?

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), "")

ruthcurran

Thank you Paul but still not working...

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.

ruthcurran

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

ruthcurran

Paul Newcome

I'm not sure I understand your question.