Color Scale

Options
Jeff Thomas
Jeff Thomas ✭✭✭
edited 12/09/19 in Smartsheet Basics

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?

 





 

Comments

  • L_123
    L_123 ✭✭✭✭✭✭
    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.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    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

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

  • L_123
    L_123 ✭✭✭✭✭✭
    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.

  • L_123
    L_123 ✭✭✭✭✭✭
    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)), "")

    conditional_format_helper.JPG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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. cheekywink

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

    Comm_2.PNG

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Nicely done! yes

    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.