Excel Conditional Formatting with Icon Sets using relative reference

[sersan]

Hi all,

I'm trying to create a conditional formatting in a column that will look to the value in the previous column to apply the traffic lights icons: green, yellow, and red.
The idea is, if value equals or higher than reference value then green, if greater than 75% of that value then yellow, otherwise red.
However, icon sets don't allow the usage of relative reference, it has to be an absolute value. The "easy" option would be to create a different rule for each cell, but that would take forever.

I'm trying to use OFFSET as a way to trick Excel, but for some reason it doesn't work...
As an example, please consider the following example:

In the conditional formating, I'm using the formula "=OFFSET($C$3,ROW()-3,0,1,1)" for the "green" and "=OFFSET($C$3,ROW()-3,0,1,1)*0.75" for yellow, with the results observed in the picture.
As you can see, cell D5 should be green!

Hope someone can find the problem, or suggest another way to make this conditional formatting work!

Many thanks to all for the help!
Regards

 

[sersan]

Sorry, figure not available...
It should be something like this:

	A	B	C	D	E
1
2
3		A	100%	79%	(yellow)
4		B	80%	46%	(red)
5		C	85%	90%	(yellow)
6		D	100%	70%	(red)
7		E	50%	15%	(red)
8		F	50%	78%	(yellow)
9		G	30%	15%	(red)
10		H	50%	40%	(red)
11		I	100%	83%	(yellow)
12		J	100%	52%	(red)
13		K	90%	15%	(red)
14		L	90%	52%	(red)
15		M	70%	75%	(yellow)
16		N	100%	64%	(red)
17		O	100%	79%	(yellow)
18		P	90%	62%	(red)

<colgroup><col span="4"><col><col></colgroup><tbody>
</tbody>