How To Use Formulas with Symbols for Sales Sheet
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.
Best 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 7599.9%, Yellow for 5074.9% and red below 50%. To break out 2549.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

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 7599.9%, Yellow for 5074.9% and red below 50%. To break out 2549.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.

@Mark Cronk Thank you very much!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 379 Global Discussions
 210 Industry Talk
 441 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 300 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!