Formula to create symbols based on parameters
Hello Community,
I'm attempting to create a formula that will create a green, red, or yellow dot symbol based on parameters and values within given cells.
The formula I have thus far is below but I continue to receive error messages
Any help would be appreciated.
Thanks!
Best Answer
-
I think I tested out all scenarios. If you find anything out of sorts and can't figure it out yourself, just let me know whether Auto is on or not and what temp it's not acting correctly with.
Example sheet:
Status formula:
=IF(AND([ARU1 HMI in Auto]@row = "Auto", [ARU1 Room Temp]@row >= 65, [ARU1 Room Temp]@row <= 77), "Green", IF(AND([ARU1 HMI in Auto]@row <> "Auto", [ARU1 Room Temp]@row >= 65, [ARU1 Room Temp]@row <= 77), "Yellow", IF([ARU1 Room Temp]@row < 65, "Red", IF([ARU1 Room Temp]@row > 77, "Red", ""))))
Answers
-
It looks like you've got conflicting formulas and the IF statement at the end doesn't have a value for true set.
Your 1st IF statement says to mark a Red symbol if temp is greater than 77.
Your 2nd IF statement says to mark a Red symbol if temp is less than 35.
Your 3rd IF statement says to mark a Green symbol if temp is less than or equal to 35 (conflicts with 2nd IF). Do you want it Green or Red if less than 35?
Your 4th IF statement has a comma after designating the cell to look at so it doesn't finish the logic statement, even without the comma there it doesn't set a condition for what to do if that cell is equal to or greater than 77.
If you explain clearly what color results you want for different temperature ranges, I can help write the formula you'd need.
-
Thanks Matt, I noticed the IF statement issue immediately following my submission. But let me know if the below is sufficient.
Green Symbol- If "ARU1 HMI in Auto" is in Auto, and "ARU1 Room Temp" is between 65 and 77
Yellow Symbol- If "ARU1 HMI in Auto" is NOT in Auto (Offline or Manual), and "ARU1 Room Temp" is between 65 and 77
Red Symbol- If "ARU1 Room Temp" is NOT between 65 and 77 (Irrespective of ARU1 HMI in Auto)
Let me know if you need additional information and thanks again for your help!
-
I think I tested out all scenarios. If you find anything out of sorts and can't figure it out yourself, just let me know whether Auto is on or not and what temp it's not acting correctly with.
Example sheet:
Status formula:
=IF(AND([ARU1 HMI in Auto]@row = "Auto", [ARU1 Room Temp]@row >= 65, [ARU1 Room Temp]@row <= 77), "Green", IF(AND([ARU1 HMI in Auto]@row <> "Auto", [ARU1 Room Temp]@row >= 65, [ARU1 Room Temp]@row <= 77), "Yellow", IF([ARU1 Room Temp]@row < 65, "Red", IF([ARU1 Room Temp]@row > 77, "Red", ""))))
-
You are truly a wizard, thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!