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!
Hello community, I need help figuring out how to set up a sheet to collect monthly comments on several projects. Each project is assigned to a different person who need to provide monthly feedback on its progress. Ideally, I would like them to receive a reminder notification on the report due date, followed by reminders if…
Is it possible that a workflow that MOVES rows to another sheet too basically do a CUT/PASTE (like in Excel), and then you are left with BLANK rows on your original sheet? I cannot think of any other LOGICAL reason why this would happen. The users enter the data from an Intake Form, it gets placed onto this sheet. They do…
Hi. I have a sheet where I am sorting dates by ascending order. But the sorting functionality doesn't seem to be working. Instead of bringing all items with date values to the top of the sheeting and sorting them in ascending order, it leaves a gap. Is it a result of the "Next Review Due" column containing a column formula…
