Ranking - Conditional Formatting

[lj080976]

Hi
I have 2 sets of data FY10 and FY11 sales by store name and I want to show, using the ICON sets, which stores have changed ranking this year to last year.
I have tried using the conditional formatting and IF Statements but to no avail.
Please could you advise how best to do it? Ideally I want to show arrows up for those that have improved, arrows down for those that have got worse and a flat arrow for no change.
Thanks.

 

[Matt Rogers]

Hi.

Maybe ?
??? = Symbol

Excel Workbook
	A	B	C
1	FY10	FY11
2	10	20	?
3	20	10	?
4	30	30	?
Sheet

 

[Matt Rogers]

Spreadsheet formulas are not completed!
I don't know why

 

[Matt Rogers]

<table valign="middle" colspan="4" style="font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:normal; font-style:normal; " border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="62,4pt"><col width="62,4pt"><col width="50,4pt"></colgroup><tbody><tr style="background-color:#cacaca"><td>
</td><td align="center">A</td><td align="center">B</td><td align="center">C</td></tr><tr><td style="background-color:#cacaca" align="center">1</td><td align="center">FY10</td><td align="center">FY11</td><td align="center">
</td></tr><tr><td style="background-color:#cacaca" align="center">2</td><td align="center">10</td><td align="center">20</td><td align="center">↑</td></tr><tr><td style="background-color:#cacaca" align="center">3</td><td align="center">20</td><td align="center">10</td><td align="center">↓</td></tr><tr><td style="background-color:#cacaca" align="center">4</td><td align="center">30</td><td align="center">30</td><td align="center">↔</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color:#eeaaaa"><td>Cell
</td><td>Formula</td></tr><tr><td>C2</td><td>=(IF(B2=A2,"↔",IF(B2>A2,"↑",IF(B2<A2,"↓"))))</td></tr><tr><td>C3</td><td>=(IF(B3=A3,"↔",IF(B3>A3,"↑",IF(B3<A3,"↓"))))</td></tr><tr><td>C4</td><td>=(IF(B4=A4,"↔",IF(B4>A4,"↑",IF(B4<A4,"↓"))))</td></tr></tbody></table>

 

[xenou]

I'm not really very well-versed in the new conditional formatting options but I did get so much working using a simple set of data with a basic comparison. I guess ultimately it comes down to getting your formula working correctly (if its possible). Sometimes I prefer to work out the formula in a regular cell so it returns TRUE or FALSE. Once you get that working in a cell, you transfer it to your conditional formatting formula (or even just refer the conditional format to this cell's result). I sometimes use a traditional hack myself, as regards screwdriver's solution above - though I have to admit the icons do look sharp - I don't know if in real life I'd find this kind of flash helpful or not. Typically once you know your reports and your stores you won't need arrows in the cells to know what to pay attention to.

<img alt="data bars" src="http://northernocean.net/etc/mrexcel/20111004_formatting.png" />

ξ