# 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. ## 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?

2. ## 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. ## 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. ## 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. ## 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

#### Posting Permissions

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