Complicated %Diff Formula

[Montrey]

Hello all,


I have a dashboard almost complete but I have a small problem. I want my %difference formula to do a few things. For example;

Ex. 2010 Vs. 2009.

If 2010 is >1 and 2009=0 then I want the Difference to show 100%

If 2010=0 and 2009=<1 then I want the difference to show -100%

If 2010=0 and 2009=0 then I want the difference to show 0%

If 2010 is >1 and 2009>1 then it should just be straightforward((10-09)/09)

If 2010 is >1 and 2009<1 then I can't just do what is mentioned directly above^ because it brings back the wrong % because the 2009 is a negative.

If 2010 is <1 and 2009 <1 then I run into the same problem as mentioned above ^.


Has anyone run into this issue before? Here is what I have so far but I can't add anymore too it because it says it is too long.


=IF(AND(G10=0,E10>1),MIN(100%),IF(AND(E10=0,G10>E10),MAX(-100%),IF(E10=G10,"0.0%",IF(AND(E10>1,G10<0),((E10-G10)/(G10)*-1),(E10-G10)/G10))))


Therefore I am coming to you guys for help! Who knew such a simple formula could be so complex.

 

[fred]

Please define

1. the "problem" with the >1 on 2010 and <1 on 2009. if there is a decrease then there is a decrease.

2. what "ideal" results you want to see. are you seeking an absolute value from the computation? Then you need to use ABS().

 

[SirJB7]

Hi, Montrey!

I've just checked in the Excel Help and you can nest up to 64 IF instructions, in Excel 2010. I rememeber to had a problem with nested ifs with a limit of 8, but it was with Excel 2002 or 2003. I used 2007 but I didn't got an error and I don't know how many I nested.

What version are you using?

Do you get an error while nesting IFs or you'd like a nice formula to accomplish the job?

Regards!

 

[Montrey]

I am using excel 2003.


I can't add anymore arguments to the formula mentioned in my original post. So I was wondering if there was an easier to way to do it. What it boils down to, is I don't want errors to be displayed and I dont want huge percentages to be displayed either. nothing greater than 1000%or -1000%. Also when dealing with differences or variance as it sometimes referred there are errors.


For example. A=1500 B=-2500

If I were to take the A and B and put them into a %difference formula of (A-b)/b. I would get an answer that is not correct because of B being negative.


Thanks for any help towards solving this problem!

 

[SirJB7]

Hi, Montrey!


Check this table:

[pre]

2009	2010	Desired	Formula
0	2	100%	100%
1	0	-100%	-100%
0	0	0%	0%
2	3	50%	50%
0,5	2	300%	300%
0,25	0,5	100%	100%

[/pre]

Although you haven't defined all the possible values, I wrote down this cases and got the same output. The formula is:

=SI(A2=0;SI(B2=0;0;SI(B2>1;1;B2));(B2-A2)/A2) -----> in english: =IF(A2=0,IF(B2=0,0,IF(B2>1,1,B2)),(B2-A2)/A2)


Regards!

 

[Montrey]

Maybe this will help.

Year2....Year1

0........0 I need 0.0% displayed

200......0 I need 100% displayed

0........200 I need -100% displayed

300......100 I need 200% displayed

-100.....200 I need -150% displayed

200.....-100 I need 300% displayed

-200....-100 I need -100% displayed

0.......-200 I need 100% displayed

-200.......0 I need -100% displayed


the % displayed i am referring too is the % difference between Year 2 and 1.

 

[SirJB7]

Hi, Montrey!

The previous formula worked for every case except last.

Here's the new one that includes it.

=SI(B2=0;SI(A2=0;0;SI(A2>1;1;SIGNO(A2)));(A2-B2)/ABS(B2)) -----> in english: =IF(B2=0,IF(A2=0,0,IF(A2>1,1,SIGN(A2))),(A2-B2)/ABS(B2))

Regards!

 

[Montrey]

WoW!!! you are awesome! Amazing!


I never knew about the Sign function! Thank you so much! So much cleaner and easier!

/praise

 

[SirJB7]

Hi, Montrey!

Very kind your last comment, but there's no secret.

I only read your definitions, wrote them on a worksheet, and began playing with the formulas. As there are all independent cases, each time I had the formula working for one of them, I copied it to all the rest. A column Difference help me a little (yes, a little, but very little... I think I'm a little lazy).

Here's the link:

http://www.2shared.com/file/QxCZlOKc/Complicated_Diff_Formula__for_.html

If your definitions had been exact and covered all the possible cases from the beginning (I'm neither blaming you nor making a complaint, just resuming what happened), with the cells in yellow it should have been enough. When you added the new examples, I followed the same procedure: adapt for one, copy to all, check if differences. One step at a time. And there appeared the SIGN function! Of course, previous formatting output as percentage.

Glad to having helped you. Just advise if any trouble.

Regards!