Hi! Not great at formulas here :)

Trying to get the formula to populate tiering (None, T1,T2, or T3) based on the cabinet total using greater than, less than ranges. =IF([Column5]12 > 55 < 91, "T3", IF([Column5]12 > 91 < 121, "T2", IF([Column5]12 > 121, "T1", IF([Column5]12 > 55, "None")))). What am I messing up?


Tags:

Answers

  • Genevieve P.
    Genevieve P. Employee
    edited 10/26/21

    Hi @KatieFields

    You'll want to add an AND statement into each of your IF statements to search between a range, like so:

    =IF(AND([Column5]@row > 55, [Column5]@row < 91), "T3", 


    You may want to include an = sign in some of these so it knows what to do if it's exactly that number. I've also swapped out your row reference to use @row for each row instead.

    Try a full formula like this:

    =IF(AND([Column5]@row >= 55, [Column5]@row < 91), "T3", IF(AND([Column5]@row >= 91, [Column5]@row < 121), "T2", IF([Column5]@row >= 121, "T1", IF([Column5]@row > 55, "None"))))


    Let me know if this works for you!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • KatieFields
    KatieFields ✭✭✭

    Hi Genevieve! Thank you for your insight!!! This is coming back as "Circular Reference". This is what I have. (I tried your exact one too with the same issue but thought maybe it was the at the end, so I flipped it and added and AND for T1 answer, but both got "Circular Reference" Error :(

    =IF(AND([Column5]@row >= 55, [Column5]@row < 91), "T3", IF(AND([Column5]@row >= 91, [Column5]@row < 121), "T2", IF(AND([Column5]@row >= 121, "T1", IF([Column5]@row < 55, "None")))))

  • Hi @KatieFields

    Would you be able to post a screen capture of your sheet? What column are you placing this formula in to, and is it possible that there's a formula in [Column5] that is looking at this one?

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • KatieFields
    KatieFields ✭✭✭

    Hi Genevieve! The cell in Column 5 is just a sum total of the rows above. See attached :)


  • Hi @KatieFields

    Thank you for clarifying! I misunderstood your sheet structure. I thought you were looking to evaluate the cell in [Column 5] for each row going down your sheet, which is why I added in @row, so you could drag this down the entire column.

    However in your instance, if you're looking just to evaluate one cell, you'll need the row number identified like you had at the start:

    =IF(AND([Column5]$12 >= 55, [Column5]$12 < 91), "T3", IF(AND([Column5]$12 >= 91, [Column5]$12 < 121), "T2", IF([Column5]$12 >= 121, "T1", IF([Column5]$12 > 55, "None"))))

    I added a $ sign in front of the 12 so that the formula will always lock to that cell. Does that make sense?

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!