Hi! Not great at formulas here :)

Options

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
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • KatieFields
    KatieFields ✭✭✭
    Options

    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")))))

  • Genevieve P.
    Options

    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?

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • KatieFields
    KatieFields ✭✭✭
    Options

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


  • Genevieve P.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!