Icon sets – conditional formatting

Hi,

Can anyone point me in the right direction on this?

I have values in column AL which contain either 1 ,2 or 3.

I would like an icon set based on the following:
1 = Red
2 = Yellow
3 = Green

Can someone advise me how to do this – I have tried format cells based on their values and to show icon sets only but this didn’t work.

I have uploaded an example.


Paul

Hello,

the values in column AL are text, not numbers. Hence the conditional formatting cannot evaluate numeric values.

Change the values to be real numbers, for example by using a formula like

=BB2+0

in AL2 and copied down. Then the traffic lights will show.

cheers,

Your formula in column BB returns text values.

=IFERROR(IF($AY2*AV2>$AY2*AU2,"3",IF($AY2*AV2=$AY2*AU2,"2","1")),"1")

You can avoid the hassle of converting text to numbers in column AL if if you return numbers in BB in the first place

=IFERROR(IF($AY2*AV2>$AY2*AU2,3,IF($AY2*AV2=$AY2*AU2,2,1)),1)