Symbols Based on Value

I am creating a P&L Sheet to evaluate Job Profitability. I want to have two columns where symbols (Red, Green, Yellow, Blue) will show the Job Health. The first column will show symbols based on the Labor Profit Health, and the Second will be based on the Total Project Health.

I would like the colors to meet the following parameters For the Labor Health:

Labor Profit % > 25% - Green

Labor Profit % between 25% and 20% - Yellow

Labor Profit % < 19% - Red (including negative percentage)

Labor Billed = 0 – “Blue”


I would like the colors to meet the following parameters For the Total Project Health:

Total Profit % > 25% - Green

Total Profit % between 25% and 20% - Yellow

Total Profit % < 19% - Red (including negative percentage)

Total Billed = 0 – “Blue”


Any help would be appreciated

Answers

  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭
    edited 09/08/22

    Hello @Micah Turner

    If I'm understanding your question correctly something like this should help

    First ensure the "Status" or Health Columns have the Symbol Properties you desire:

    Then have the Columns with Percentages in Percentage Format.

    Lastly this formula. You can modify it to suit your needs better if needed.

    =IF(Percentage@row > 0.24, "Green", IF(Percentage@row > 0.19, "Yellow", IF(Percentage@row = 0, "Blue", IF(Percentage@row > -0, "Red", "Red"))))


    Let me know if you found this helpful!

    https://www.linkedin.com/in/zchrispalmer/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!