Color Scale

Options
✭✭✭
edited 12/09/19

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?

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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)), "")

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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:

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.

• ✭✭✭✭✭✭
Options

Nicely done!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options