Better / Worse Variance issues (ex: +7,123% but want +100%)

[CRichards]

Hello,

First post. I am having issues reporting a better / worse variance percentage. I'm taking the difference from this year vs. last year. I take the two differences and calculate the variance % (Difference between 2014 vs 2013/2013 number). This could be positive or negative. The problem I'm getting -567.1% or 7,123%. I would like 100%+ or (100%+) if over (+/-)100%.

Example:

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl72, width: 64"]A[/TD]
[TD="class: xl72, width: 64"]B[/TD]
[TD="class: xl72, width: 64"]C[/TD]
[TD="class: xl72, width: 64"]D[/TD]
[/TR]
[TR]
[TD="class: xl69"]2014 Act.[/TD]
[TD="class: xl69"]2013Act[/TD]
[TD="class: xl69"]B/W[/TD]
[TD="class: xl68"]Var %[/TD]
[/TR]
[TR]
[TD="class: xl70"]1[/TD]
[TD="class: xl70"]0.25[/TD]
[TD="class: xl70"]0.75[/TD]
[TD="class: xl71"]300.0% [/TD]
[/TR]
</tbody>[/TABLE]


Help is greatly appreciated. Thank you!

 

[bbott]

Try this in D2:

=IF(C2/B2-1<-1,"(100%+)",IF(C2/B2-1>1,"100%+",C2/B2-1))