Use IF THEN Formula
IDK what I'm doing wrong as it Allllmost works.
Trying to create a formula to say, If you look at this overall score...
if it's 0  54 = Tier 3
If it's 55  90 = Tier 2
If it's 91 and beyond = Tier 3
I'm using this formula... it works except it won't display tier 3...
=IF(Answer21 < 55, "Tier 3", IF(Answer21 > 54, "Tier 2", IF(Answer21 > 91, "Tier 1")))
What am I missing?
Thank you!!
Answers

Hi @Bria Berger,
Try this:
=IF(AND(Answer@row >= 0, Answer@row <= 54), "Tier 3", IF(AND(Answer@row >= 55, Answer@row <= 90), "Tier 2", IF(Answer@row >= 91, "Tier 1", "")))
Hope that helps!
Ray

Hello @Bria Berger
You have Tier 3 listed twice in your description so I am not exactly sure which way you want it. I am defining it is:
 054 = Lower Tier
 5590 = Middle Tier
 91+ = Higher Tier
You can use the below formula and replace the Lower Tier, Higher Tier, and Middle Tier text as you need.
=IF(Answer@row < 55, "Lower Tier", IF(Answer@row > 91, "Higher Tier", "Middle Tier"))
A couple things to note:
 I am using @row rather than the row #s. This makes the formula always look at the data in the row it is in.
 I am only using 2 IF() statements and having "Middle Tier" populate as the Value if False for the second IF() statement.
It looks like this in the Sheet:
_____________________________________________________________________________________________
👨🏼💻 Dan Palenchar  School of Sheets Solutions Consulting  Smartsheet Aligned Gold Partner
If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).
🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS  If you have a follow up response attention use @Dan Palenchar so I get notified of your reply!
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

@Ray Lindstrom Thanks for the quick response. This is giving me a circular reference error.

Hi @Bria Berger ,
Is it that the formula won't display Tier 3 or Tier 1? Based on the formula you provided, I supsect you will never see Tier 1 because any value over 54 will show Tier 2 and the formula would stop, even if the value is 92 or higher.
Try this. =IF(Answer21 < 55, "Tier 3", IF(AND(Answer21 > 54, Answer21 < 91), "Tier 2", IF(Answer21 > 91, "Tier 1")))
Hope this helps,
Dave

@Dan Palenchar  That worked perfectly and appreciate you calling out the note about the @row vs. my cell. In this case, I only have one cell calculating my sum so I needed to reference the that one cell. As soon as I cleaned that up, no more circular reference error and my tiers are showing up perfectly.
have a great rest of your day and weekend!

I tested my formula before replying.
=IF(AND(Answer@row >= 0, Answer@row <= 54), "Tier 3", IF(AND(Answer@row >= 55, Answer@row <= 90), "Tier 2", IF(Answer@row >= 91, "Tier 1", "")))
Circular reference from my formula means you must have pasted the formula into the Answer column. The formula was for a separate column such as "Tier" which would populate based off your Answer column. And I applied it as a column formula.
Here's what my formula produces when in a "Tier" column. Maybe I had misunderstood your ask though.
Glad you got it figured out either way.
All the best,
Ray
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 379 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
 303 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!