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?
Answers
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!