How To Use Formulas with Symbols for Sales Sheet

Meny Hoffman
Meny Hoffman âś­âś­âś­âś­

Hello,

I created a sales projection vs actual sheet for 2021, I would like to use a formula with symbols to automatically change based on percentage.

So if it's at 25% one, 50% one, 75% another, and by 100% show green, based on my column names, what would be the best setup for the formula?

I greatly appreciate your help.

Tags:

Best Answer

  • Mark Cronk
    Mark Cronk âś­âś­âś­âś­âś­âś­
    edited 01/01/21 Answer âś“

    Hi @Meny Hoffman ,

    The basic formula in a 4 color symbol column would be:

    =IF([actual sales]@row/[projected sales]@row>=1, "Blue", IF([actual sales]@row/[projected sales]@row>=0.75, "Green", IF([actual sales]@row/[projected sales]@row>=0.5, "Yellow", "Red")))

    You may need to adjust the color names depending upon what symbol set you use. You may also want to adjust the ranges. The formula above will produce blue for 100% and above, Green for 75-99.9%, Yellow for 50-74.9% and red below 50%. To break out 25-49.9% you'd add another IF statement before the default answer, "Red".

    Work?

    Happy New Year!

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk âś­âś­âś­âś­âś­âś­
    edited 01/01/21 Answer âś“

    Hi @Meny Hoffman ,

    The basic formula in a 4 color symbol column would be:

    =IF([actual sales]@row/[projected sales]@row>=1, "Blue", IF([actual sales]@row/[projected sales]@row>=0.75, "Green", IF([actual sales]@row/[projected sales]@row>=0.5, "Yellow", "Red")))

    You may need to adjust the color names depending upon what symbol set you use. You may also want to adjust the ranges. The formula above will produce blue for 100% and above, Green for 75-99.9%, Yellow for 50-74.9% and red below 50%. To break out 25-49.9% you'd add another IF statement before the default answer, "Red".

    Work?

    Happy New Year!

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Meny Hoffman
    Meny Hoffman âś­âś­âś­âś­

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!