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

=IF(Answer21 < 55, "Tier 3", IF(Answer21 > 54, "Tier 2", IF(Answer21 > 91, "Tier 1")))

What am I missing?

Thank you!!

Tags:

Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    Options

    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
    SoS | Dan Palenchar ✭✭✭✭✭✭
    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

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

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

  • Bria Berger
    Bria Berger ✭✭✭✭
    Options

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

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    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.

    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
    Bria Berger ✭✭✭✭
    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!

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

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!