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

GingaNinga

New Member
Joined
Sep 1, 2017
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
1603898016827.png


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
    1603897984997.png
    5.6 KB · Views: 1

Some videos you may like

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
Joined
Dec 30, 2008
Messages
12,496
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 1, 2017
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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?

1603908368363.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,496
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top