Symbol Formula for values

n7teixeira

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?

brianschmidt

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

brianschmidt

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!:)

n7teixeira

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?

brianschmidt

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?

n7teixeira

Yes thanks a bunch that worked perfectly!

n7teixeira

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!!

brianschmidt

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