Nested IF formula problem

arsohn

New Member
Joined
Jul 6, 2015
Messages
1
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%
 
Last edited:
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)))))
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top