Hi! Not great at formulas here :)

KatieFields

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?

Genevieve P.

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

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

Genevieve P.

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?

KatieFields

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

Screen Shot 2021-10-26 at 9.16.13 AM.png

Screen Shot 2021-10-26 at 9.16.17 AM.png

Genevieve P.

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