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

bon

Board Regular
Joined
Jan 28, 2005
Messages
97
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top