Iferror + if formula

[drluke]

I am calculating the % change between unit sales year-on-year, but if the movement is greater than 500% either way I don't want the formula to do anything, and similarly if there are no figures to compare in prior years then I don't want the formula to return an error (because of division by 0). So far I have come up with:

=IFERROR(IF(D3/C3>5,"",D3/C3),"-") It seems to work ok for movements greater than 500%, but

How do I add the 'smaller than -500%' part to the formula? or
Can I use a different more effective formula instead?

 

[AlanY]

try

=IFERROR(IF(abs(D3/C3)>5,"",D3/C3),"-")

 

[drluke]

I changed my formula to the one that was suggested, and it works well except for some calculations where the result is more than 1000% either way.
Any ideas how I can change the formula to cover this scenario?

 

[AlanY]

the formula exclude results over 500% that should include 1000%, have I missed something?

 

[drluke]

Maybe I've missed something!
I have these 2 calculations as examples:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]25,555[/TD]
[TD]-1,408[/TD]
[TD]-1915%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]-6,381[/TD]
[TD]-275[/TD]
[TD]2223%[/TD]
[/TR]
</tbody>[/TABLE]

The formulas at both D2 and D3 are

=IFERROR(IF(ABS(B2-C2)/C2>5,"",(B2-C2)/C2),"-") and
=IFERROR(IF(ABS(B3-C3)/C3>5,"",(B3-C3)/C3),"-")

 

[AlanY]

try

=IFERROR(IF(ABS((B2-C2)/C2)>5,"",(B2-C2)/C2),"-")