% difference between two values, which can either be positive or negative

[GingaNinga]

Hello there - I have the need to determine the % between two different values (Actuals / Target). This normally works if both values are positive, or both are negative.
I have been able to create an IF statement to handle most of the exceptions I am seeing (the formulas contained in columns c & d are below the screenshot)

There is however one exception that I am unable to solve for, and this when the Actual is positive and the Target is a negative value.

You can see the issue in the above screenshot where my target is negative, my actual is positive, but I am returning a negative % to Target result. The result in D3 should be 175.8%, and D4 should be 140% I believe

Below are the current formulas I am applying to help deal with the positive and negative integers:

Column C Formula: =IF(A2<0,0.000000001,A2)
Column D Formula: =IFERROR(IF(B2>A2,B2/A2,(IF(B2/C2<0,0,B2/C2))),1)

 

[jasonb75]

This give the expected results (slight difference on D3) but may not work as required with positive target and negative actual.

=IFERROR(IF(B2>A2,IF(SIGN(A2)<>SIGN(B2),ABS(A2)/ABS(B2),B2/A2),(IF(B2/C2<0,0,B2/C2))),1)

 

[GingaNinga]

IFERROR(IF(B2>A2,IF(SIGN(A2)<>SIGN(B2),ABS(A2)/ABS(B2),B2/A2),(IF(B2/C2<0,0,B2/C2))),1)

This is great! Thanks for that, this has solved all but 1 of the issues, which I did not expect to encounter, in cell I197 (53.1%)

I believe that the value should be around 188% (Actual is +4.9, vs -2.6), is there a way around this without impacting the success on all the other values?

 

[jasonb75]

Without an explanation of how you're arriving at the figures that you believe are correct this is only a guess, it might be possible to trim it down a little without compromising the results.

=IFERROR(IF(B2>A2,IF(SIGN(A2)<>SIGN(B2),MAX(ABS(A2),ABS(B2))/MIN(ABS(A2),ABS(B2)),B2/A2),(IF(B2/C2<0,0,B2/C2))),1)