Symbol Formula for values
I need to create Symbol Column Formula to display the following color symbols based on data in another column:
Red symbol If value is in Vacation balance column is>= 175
Yellow symbol If value in Vacation balance column is< 175 AND >= 160
Green symbol if value in Vacation balance column is <160
Help?
Best Answer
-
Hmm, the formula looks right. Are you able to share a screenshot of the sheet (with any sensitive info redacted)? Are there rows where the country column shows something other than either "USA" or "PRI". You could also try the first statement using an AND function:
=IF(AND(Country@row <> "USA", Country@row<>"PRI"), "Gray", IF([VacationBalance]@row >=175, "Red", IF([Vacation Balance]@row >= 160, "Yellow", IF([VacationBalance]@row <160, "Green"))))
Answers
-
In your symbol column, I would place the following formula into a cell and then right-click the cell and convert to a column formula. Note that the Vacation balance column is represented with those exact words in the formula. You'll want to make sure you reference the exact name of the column that data is in.
=IF([Vacation balance]@row>=175, "Red", IF([Vacation balance]@row>=160, "Yellow", "Green"))
Hope this helps!:)
-
thank you that worked!
One follow up, I would also like to include within the column formula to display the following:
Gray symbol if value in Country column is not "USA"
Any ideas?
-
That's definitely doable! First, you'll want to make sure your symbol column is set up with all those color options. I think there are options with less color symbols and some with more. Then, you can use the following formula:
=IF(Country@row<>"USA", "Gray", IF([Vacation balance]@row>=175, "Red", IF([Vacation balance]@row>=160, "Yellow", "Green")))
Bear in mind that nested IF statements like this follow order of operations. It tests the first statement and then if untrue, it tests for the conditions listed in the next before moving onto the next, etc… So, by having the If statement that tests if the country is not USA at the front of the formula, any rows that are not "USA" in the country will always show as gray regardless of vacation balance. If you want to consider both factors, the formula would likely need to be edited further with AND or OR functions incorporated.
Does that make sense?
-
Yes thanks a bunch that worked perfectly!
-
another edit…how do I edit the formula to show the Gray symbol if value in Country column is not "USA" or "PRI" ?
I tried the below but it returns all values as gray:
=IF(OR(Country@row <> "USA", Country@row<>"PRI"), "Gray", IF([VacationBalance]@row >=175, "Red", IF([Vacation Balance]@row >= 160, "Yellow", IF([VacationBalance]@row <160, "Green"))))
Thanks!!
-
Hmm, the formula looks right. Are you able to share a screenshot of the sheet (with any sensitive info redacted)? Are there rows where the country column shows something other than either "USA" or "PRI". You could also try the first statement using an AND function:
=IF(AND(Country@row <> "USA", Country@row<>"PRI"), "Gray", IF([VacationBalance]@row >=175, "Red", IF([Vacation Balance]@row >= 160, "Yellow", IF([VacationBalance]@row <160, "Green"))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!