Results 1 to 5 of 5

Three color gradient scale conditional formatting based on value in another cell

This is a discussion on Three color gradient scale conditional formatting based on value in another cell within the Excel Questions forums, part of the Question Forums category; Hello, I'm creating a scorecard for sales and would like to use conditional formatting to visually enhance. For each percentage ...

  1. #1
    bon
    bon is offline
    Board Regular
    Join Date
    Jan 2005
    Posts
    93

    Default Three color gradient scale conditional formatting based on value in another cell

    Hello,
    I'm creating a scorecard for sales and would like to use conditional formatting to visually enhance. For each percentage score reached, an if statement creates a comment ("premium", needs improvement" etc) each of the 5 possible comments has a number 1-5 associated with it. In A77 a Vlookup formula takes the statement from B76 and assigins the 1-5 value. I would like to use numbers 1-5 to create the a three color cond format that uses the min-midpoint-max gradient to shade the statement in B76. Have tried formula entries etc but not working. Any possibility to do this?
    thanks in advance, Bill

  2. #2
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,764

    Default Re: Three color gradient scale conditional formatting based on value in another cell

    You're going to struggle to put a fancy 3 colour gradient conditional format on cells which don't actually contain the values themselves.
    It would be quite easy to put one of 5 set colours of your choice in another cell based on a value in another cell or cells, based simply on the numbers 1 to 5, it would also be possible also to look at the max and min of other scores to determine which of a handful of a set colours of your choice to use, but it would be quite difficult to get the smooth gradient of colours that Excel gradient conditional format gives.
    Which are you going to go for?

  3. #3
    bon
    bon is offline
    Board Regular
    Join Date
    Jan 2005
    Posts
    93

    Default Re: Three color gradient scale conditional formatting based on value in another cell

    Hello, thanks, I was wondering if this was a bad path to take. If I was to do a color assigned to each number in the adjacent cell should I use an "if"?

    thanks for the assist and providing options.

    -Bill

  4. #4
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,764

    Default Re: Three color gradient scale conditional formatting based on value in another cell

    Yes, an if is probable, though you could just use the likes of A3=4 which will return True or False, which is what conditional formatting wants to see if you're using a formula therein.

    You could use a more complex formula to see where a particular value lies among its bretheren; for example using something along the lines of:
    =PERCENTRANK.INC($E$4:$E$24,E4,1)
    where E4 here is the value in question and the range E4:E24 contains its bretheren, the 1 means give the result to 1 decimal place; this will return a value from 0 to 1 meaning you could have 11 cf conditions like
    =PERCENTRANK.INC($E$4:$E$24,E4,1)=0
    =PERCENTRANK.INC($E$4:$E$24,E4,1)=.1
    =PERCENTRANK.INC($E$4:$E$24,E4,1)=.2
    etc.
    formatting a set colour of your choice for each.

  5. #5
    bon
    bon is offline
    Board Regular
    Join Date
    Jan 2005
    Posts
    93

    Default Re: Three color gradient scale conditional formatting based on value in another cell

    That looks like a slick way to get what I was looking for. Thanks for the advice and explaination of the solution. I'm going to tinker with that, should be a good way to achieve what I would like to do.

    much appreciated, Bill

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com