Use IF THEN Formula

Bria Berger

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!!

Ray Lindstrom

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

SoS | Dan Palenchar

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:

1. 0-54 = Lower Tier
2. 55-90 = Middle Tier
3. 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:

1. I am using @row rather than the row #s. This makes the formula always look at the data in the row it is in.
2. 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!

Bria Berger

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

DKazatsky2

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

Bria Berger

@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!

Ray Lindstrom

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