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.

Store	Actual	Plan
1	20000	17500
2	25000	20000
3	32000	30000
4	15000	25000
5	19000	20000

<tbody>
</tbody>

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