conditional formatting - Icon set

[ashani]

Hi
i'm using the conditional formatting Icon set option to to have arrows with the help of the formula below, which is working fine. However, when both cell values are the same than I want Amber dot but it's coming up as Red dot. Is there anyway I can fix it ?

=IF(OR(C5>B5,C5<B5),1,0)

I'm looking for the following :

C5 higher than B5 = show green dot
c5 lower than b5 = show red dot
c5 equals to b5 = amber dot

thank you

 

[etaf]

does this work for you
=IF(C5>B5,2,IF(C5<B5,0,1))

XL2BB doe not seem to show icons, so added an image

Book7
	B	C	D	E
4
5	20	1	0
6	1	20	2
7	20	20	1
8
Sheet1

Cell Formulas
Range	Formula
D5:D7	D5	=IF(C5>B5,2,IF(C5<B5,0,1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D23	Other Type	Icon set		NO

 

[ashani]

hi Etaf

thank you for the quick response.

It works perfectly - the only thing is if B5 value is zero 0 and C5 value is zero 0 = is it possible to have red dot instead of amber ?

Thank you once again.

 

[etaf]

i hsve added an OR(AND( to also test if both C5 and B5 = 0
Also added if C5 and B5 is blank
BUT what happens if Just , C5 or B5 i blank - thats seen as zero

=IF(AND(C5="",B5=""),"",IF(C5>B5,2,IF(OR(AND(C5=0,B5=0),C5<B5),0,1)))

 

[ashani]

yeah that's correct if both cell is blank than it will be 0
thank you

 

[etaf]

you are welcome

BUT if one cell is blank, that will also measure as zero

 

[ashani]

amazeing - thank you so much

 

[etaf]

you are welcome