Excel 2016 - Conditional Formatting Icons with Formula

[MrJeremy]

I'm needing help with the Conditional Formatting - 3 Circle Icons.

I am trying to get it to work based off a formula. Such if cell B2 is >= cell B3 it will be the green circle in cell B2. If it's 85%-100% it will be the yellow, or less than 85% it will be red.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Actual[/TD]
[TD]Plan[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]20000[/TD]
[TD]17500[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]25000[/TD]
[TD]20000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]32000[/TD]
[TD]30000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]15000[/TD]
[TD]25000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]19000[/TD]
[TD]20000[/TD]
[/TR]
</tbody>[/TABLE]

So in B2 ( Actual ), it would be green from store 1, green for store 2, green for store 3, red for store 4, and yellow for store 5.

 

[wideboydixon]

In D2, type the following formula:

=$B2/$C2

Copy this formula down as far as necessary. Now select all the cells in column D from D2 downwards and select Conditional Formatting>Icon Sets>Three Circles Set. Now go to Conditional Formatting>Manage Rules>Edit Rule. Do the following:

1. Check the box that says "Show Icon Only"
2. Change the first type to Number and the Value from 67 to 1
3. Change the second type to Number and the Value from 33 to 0.85
4. Click OK and OK

Hope that helps

WBD

 

[MrJeremy]

Thanks for the quick reply. That is a work around I was looking at, but is there any way to have the icon show up actually in the B2 cell with the actual instead of a separate cell?

 

[wideboydixon]

I don't think so. You can't use relative references in conditional formatting icon sets so you can't reference the value in column C.

WBD