Nested IF formula problem

[arsohn]

Hi, to start, I am using Windows 7 with Excel 2013.

I have a table that setup as follows, with each month being a separate column header and 2015,2014, and Trend as my row headers

January February March April May June
2015 6 2 4 1 0 3
2014 2 4 2 2 4 0
Trend 66.67% -100% 50% -100% -400% 300%

My current formula resides in the Trend rows and is as follows

=IF(B2=0,B1*100%,IF(B1=0,B2*-100%,IF(B2>0,(B1-B2)/B1))) - This of course updates column letter each cell over

This worked great, however after submitting it for review, my manager came back and requested a change, basically he never wants to see a trend total higher than 100% or lower than -100%, so I'm not sure on the best way to proceed. I tried coming up with a IF(AND.... formula to work but I couldn't get it to work. Below are the updated conditions he would like the report to follow, I feel like I'm stuck in the middle of a hard place so any help would be greatly appreciated.

If 2015 is greater than 2014 AND 2014 equals 0, show 100%
· If 2015 is greater than 2014, AND 2014 is greater than 0, take (2015-2014)/2014
· If 2015 is less than 2014, AND 2015 equals 0, show -100%
· If 2015 is less than 2014, AND 2015 is greater than 0, take (2015-2014)/2014
· If 2015 is equal to 2014, show 0%

So this is how he would like to see the report look
January February March April May June
2015 6 2 4 1 0 3
2014 2 4 2 2 4 0
Trend 66.67% -100% 50% -100% -100% 100%

 

[AlanY]

try this

=IF(IF(B3=0,B2*100%,IF(B2=0,B3*-100%,IF(B3>0,(B2-B3)/B2)))>100%,100%,IF(IF(B3=0,B2*100%,IF(B2=0,B3*-100%,IF(B3>0,(B2-B3)/B2)))<-100%,-100%,IF(B3=0,B2*100%,IF(B2=0,B3*-100%,IF(B3>0,(B2-B3)/B2)))))