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!
👨🏼💻 Dan Palenchar  School of Sheets Solutions Consulting  Smartsheet Aligned Gold Partner
If this helped, help me & the SSC by accepting it and reacting w/💡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 use @Dan Palenchar so I get notified of your reply!

@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
Check out the Formula Handbook template!