Conditional Formatting with directional icon sets

[cnorthcutt]

Hello! I'm new to the forum. Hopefully someone will be able to help me. I have a table set up that shows three years of data,and I want to compare the las two columns. Column C shows 2013 and Column D shows 2014. Column E is where I have entered the formula, SIGN(D5-C5), and applied conditional formatting using directional icon sets. I cannot figure out why some of the arrows are not pointing in the right direction. I.e. D10 and C10 are the same so the arrow should be yellow, but the green arrow is showing.

The spreadsheet is attached. Please Help!!

[FDibbins]

Hi and welcome to the forum

Your rules are...
green UP arrow for values >67%
yellow SIDE arrow for values >=33% and <67%
red DOWN arrow for anything < 33%

well. 0 is less than 33%, so it shows red DOWN

Edit: also, by using...
=SIGN(D5-C5)
all you will ever get is 1 or 0, so the arrows will only ever be up or down

[FDibbins]

Try this instead of your SIGN() formula....

=IF(OR(C5=0,D5=0),"",(D5-C5)/D5)

[cnorthcutt]

Thanks, but I can't get this to work either. The spreadsheet titled "Working" shows how I want it to work. The method works on some spreadsheets, but not on others. I can't figure out why.