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:

1	B	C	D
2	25,555	-1,408	-1915%
3	-6,381	-275	2223%

<tbody>
</tbody>

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),"-")