XL07 - Icon sets wrong results?

[SuperFerret]

Hi there, I've just been using Icon sets for some reports for a management meeting, but I've come across a problem.

I used a formula to determine the varience between 2010 and 2009 figures, using icon sets to show the difference... but I seem to be getting the wrong results?

Example data with simulated icon set arrows:
<TABLE style="WIDTH: 250pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=332><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" span=2 width=89><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=89 align=right>£2,084,562.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 67pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=89 align=right>£2,082,482.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=90 align=right>£2,080.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl73 width=64>↑</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20 align=right>£10,862.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>£9,035.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>£1,827.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl74>↑</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20 align=right>£147,835.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>£165,843.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>-£18,008.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl74>↓</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=20 align=right>83.00%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>83.00%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>0.00%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl74>↑</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=20 align=right>1.25%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>1.35%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>-0.10%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl74>↑</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 height=21 align=right>75.00%</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 align=right>60.00%</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 align=right>15.00%</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl75>↑</TD></TR></TBODY></TABLE>

A is the 2009 figures
B is the 2010 figures
c is where I've just put a simple calulation A2-B2 for example
D is what my icon sets are giving me

So is it me or is this giving me wrong information? I can't find a way to make the icon sets show the true results. By my thinking the first should be DOWN, second should be DOWN, third should be UP, fourth should be SAME, fifth should be UP and Sixth should be DOWN.

Now I remember why I HATE icon sets

 

[JackDanIce]

Hi Stalker,

Have 07 at home so can check tonight; asking the obvious but have you tried searching Google for your answer?

Most of your expected answers are the opposite of what's given so perhaps error in the formula or how you've set your icons up?

Can't offer any definitive answer right now, sorry!

 

[SuperFerret]

Thanks for looking anyway, I hope after you've been home you can help! I tried searching on Google but can't seem to find an answer, just more people with the same problem...which isn't promising.

Setup of the icons may be the issue, I can't see a way to actually change the settings much so that could be the problem.

I've managed a workaround with using symbols in a nested IF, but apparently that's not pretty enough.

p.s NOT A STALKER!!

 

[SuperFerret]

Found a better workaround (in C1):


=IF(B1>A1,2,IF(B1=A1,1,IF(B1<A1,0)))< p> < A1,0)))<A1,0)))< p>

Then just use:
UP when value is >= 2 (number)
SAME when <2 and >= 1 (number)
DOWN when <1

Checked the show icon only and voila!

Still would like to know why it wasn't reacting how I expected.

 

[JackDanIce]

Pretty cool work around. I'm out tonight but if I remember when I get home I'll have a look

 

[SuperFerret]

Going out on a school night? Alcky! Have a drink for me!

If you don't remember, don't worry... at least I have a solution the management are happy with! haha

 

[Norie]

What 'way' are you trying to calculate the variance?

Change from 2009 to 2010 or 2010 ro 2009?

If it's 2009 to 2010 shouldn't the calculation be =B2-A2?

 

[SuperFerret]

What 'way' are you trying to calculate the variance?

Change from 2009 to 2010 or 2010 ro 2009?

If it's 2009 to 2010 shouldn't the calculation be =B2-A2?

I tried that, and it corrects some of the results but not others. It always to me seems at it's most unhappy with percentages, although I'm not sure why. I did try =B2-A2 =A2-B2 an IF formula (before the one I've ended up using) but it always seemed to fall down on one or two... and not always the same ones?

I must admit I've never had good experiences with icon sets and I personally prefer to see the actual figures as opposed to pretty pictures, but I know this is going to keep coming back so I need to try and understand if it's the way I'm doing it or something else.

 

[Norie]

But, apart from not showing the correct icons, isn't that the right formula?

 

[SuperFerret]

But, apart from not showing the correct icons, isn't that the right formula?

Yes, and when you have it show the results it shows up correctly, and gives the actual varience.

It's only when you try to add the icon sets (the bit the management insist on) that it displays the correct varience i.e. a decrease of £2,080.00 but the icon shows an UP... I know this is most likely because it is greater than 0, so it thinks it's the right icon, but it's not the right icon for what I want it to show.

Is it because you should always take smallest from biggest?