Excel formula Help Required

[TechSupp]

I have a column of cells in a sheet with cells coloured red, green and amber and I want another cell to show if this is up or down by showing an arrow, i.e. cell A1 is Red, cell B1 will show colour index number, cell D1 will show a down arrow (formatted cells with WingDings3 - 6 gives a down arrow)

I can get the colour index of the cell fill to get a number i.e. =ColorIndex(A1) would give 3 but I need to compare each of the three colours to show the correct arrow:

 

if B1 = 4 (GREEN) then cell = 5 (up arrow)

if B1 = 44 (AMBER) then cell = 1 (arrows side to side)

if B1 = 3 (RED) then cell = 6 (down arrow)

 

My best stab is =OR(IF(B1=4,5),IF(B1=44,1),IF(B1=4,5))

 

Cant for the life of me get it to work! All I can get is for the required cell to show a range of characters (when formatted correctly to read shows the word 'true')

[spadam]

Not sure I understand fully but would 3 nested IF statements do what you want?

[spadam]

IF(B1=4,5,IF(B1=44,1,IF(B1=3,6)))

[Seb1780]

Ninja'd

[TechSupp]

Thanks I'll give it a try, tried nested if's but not quite like that.

[123sch]

What about a VLOOKUP of a color list and then you can have a range of colours attributed to your symbols and adapt later if needed.

[TechSupp]

IF(B1=4,5,IF(B1=44,1,IF(B1=3,6)))

 

Tried that and works a treat, thanks