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

#### GingaNinga

##### New Member
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)

#### Attachments

• 1603897984997.png
5.6 KB · Views: 1

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### jasonb75

##### Well-known Member
This give the expected results (slight difference on D3) but may not work as required with positive target and negative actual.
Excel Formula:
``=IFERROR(IF(B2>A2,IF(SIGN(A2)<>SIGN(B2),ABS(A2)/ABS(B2),B2/A2),(IF(B2/C2<0,0,B2/C2))),1)``

#### GingaNinga

##### New Member
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

##### Well-known Member
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.
Excel Formula:
``=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)``

Replies
2
Views
64
Replies
0
Views
472
Replies
3
Views
52
Replies
1
Views
131
Replies
10
Views
893

1,128,129
Messages
5,628,865
Members
416,347
Latest member
AT2021

### 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.

### Which adblocker are you using?

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

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