# Use IF THEN Formula

Options
✭✭✭✭

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

What am I missing?

Thank you!!

Tags:

• ✭✭✭✭✭✭
Options

Hi @Bria Berger,

Try this:

Hope that helps!

-Ray

• ✭✭✭✭✭✭
Options

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

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.

PS - If you have a follow up response use @Dan Palenchar so I get notified of your reply!

• ✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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.

Hope this helps,

Dave

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 10/08/23
Options

I tested my formula before replying.

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!