I read an old thread from 2018 about this, but is there a plan to provide users the ability to do conditional formatting using a RYG color scale based on high/low values?
I haven't heard anything about this but I have used the current conditional formatting with stacked if statements to achieve the same result. It can be somewhat lengthy to build, but it works just fine for me.
Hi Jeff,
Great idea! That would be a great addition to Smartsheet features.
Please submit an Enhancement Request when you have a moment.
Hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
Depending on your setup, have you thought about building a table out and using an INDEX/MATCH? Have you looked into RANKEQ or RANKAVG?
.
I have a sheet where I built out a table to assign a number of 1 - 10 based on a number in a different column. I basically use MIN and MAX to set my ranges then some basic formulas to establish the highpoint of each rank. Then I use a MIN(COLLECT(......)) to pull the lowest range for each number that is higher than my other cell and use that number in an INDEX/MATCH to pull my rank.
Basically it looks like the below. For ease of explanation, I am going to say I am using 10 different ranks, my MIN is 1 and my MAX is 100.
TABLE:
Rank Range
1 10
2 20
3 30
4 40
5 50
6 60
7 70
8 80
9 90
10 100
=INDEX(Rank:Rank, MATCH(MIN(COLLECT(Range:Range, Range:Range, @cell >= Number@row)), Range:Range, 0))
So if my number is 55, the MIN/COLLECT will grab the 60 and use that to MATCH on for the INDEX.
Definitely a lot easier to edit than a long nested IF.
And since I use formulas to adjust my Range values based on the MIN and MAX found in my Number column, I have essentially automated ranking my numbers as 1 - 10. From there, conditional formatting is very easy to set up.
Yeah I thought of that while typing my answer, but the solutions I built were before those formula were introduced so I haven't tried building it yet. You are right though, that would be a much simpler way to get the desired result.
Went back and looked at my old formula and sheet I was thinking of. It is still working well, but is almost comical in length, and it only posts 3 colors (red yellow green)
=IFERROR(IF(ISBLANK([Completion Date 1]10), IF([Due Date 1]10 < TODAY(), 11), IF([Completion Date 1]10 > [Due Date 1]10, 12)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 2]10), IF([Due Date 2]10 < TODAY(), 21), IF([Completion Date 2]10 > [Due Date 2]10, 22)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 3]10), IF([Due Date 3]10 < TODAY(), 31), IF([Completion Date 3]10 > [Due Date 3]10, 32)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 4]10), IF([Due Date 4]10 < TODAY(), 41), IF([Completion Date 4]10 > [Due Date 4]10, 42)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 5]10), IF([Due Date 5]10 < TODAY(), 51), IF([Completion Date 5]10 > [Due Date 5]10, 52)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 6]10), IF([Due Date 6]10 < TODAY(), 61), IF([Completion Date 6]10 > [Due Date 6]10, 62)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 7]10), IF([Due Date 7]10 < TODAY(), 71), IF([Completion Date 7]10 > [Due Date 7]10, 72)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 8]10), IF([Due Date 8]10 < TODAY(), 81), IF([Completion Date 8]10 > [Due Date 8]10, 82)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 9]10), IF([Due Date 9]10 < TODAY(), 91), IF([Completion Date 9]10 > [Due Date 9]10, 92)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 10]10), IF([Due Date 10]10 < TODAY(), 101), IF([Completion Date 10]10 > [Due Date 10]10, 102)), "")
My outlined solution was also build pre-RANK functions.
It takes a little bit of time to set up if you are automating the ranges based off of the MIN and MAX, but overall it provides for a lot of flexibility and very low maintenance.
Haha. That is rather long. Good thing your column names aren't any longer, or you might hit that 4,000 character per cell limit.
Talking about it got me curious as to my exact setup. Since it is fully automated, I haven't had to touch it in a while.
Table Rank Table Range
0 f
1 f
2 f
3 f
4 f
5 f
6 f
7 f
8 f
9 f
10 f
I am actually using 11 different ranks.
0 is '= MIN(Score:Score)
10 is '= MAX(Score:Score)
1 - 9 are basically 10%, 20%, 30%, etc....
To get the percentages, I just used
=VALUE("." + [Table Rank]@row) * MAX(Score:Score)
Then my INDEX/MATCH in my rank column was as above with the appropriate column name changes. I manually entered the numbers of 0 - 10 in my rank column so I could go ahead and set up the conditional formatting for each rank to show shades of red, orange, and green depending on the rank.
Nicely done!
Thanks!
I have a workspace that I created with two subfolders. A user needs to have access to only one of them but when I have that folder selected, I can only share the entire workspace with them. Is there a secret way that I can share the parent folder and ensure that any new files created will be shared with the same people? I…
I have a Pro plan but I cannot create ANY reports. The system does not allow me to choose a folder, and consequently a sheet to create a row report and suggests an upgrade to Business or Enterprise. I'm quite new to Smartsheet and feel a bit disappointed with this limitation, or maybe I'm just doing something wrong. Alberto
I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes. On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken…
