Figuring out pecentage differences with negative and postive numbers

NikToo

New Member
Joined
Sep 24, 2015
Messages
48
Office Version
  1. 365
This is seriously baking my noodle...

I have two numbers, a target and a forecast. They can each be positive or negative numbers. I need to figure out the percentage difference between them but I'm getting myself down the wrong route I think and could really do with another perspective.

The problem is that say for example the target is -5000 and the forecast is +50000. I then want a positive 1000% result, but simply dividing 50000/-5000 gives me -1000%, which doesn't look so good.

Conversly, if the target is -5000 and the forecast -15000, I want a negative -300%. And if the target is -5000 and the forecast 0, I need a positive result (not quite sure how that would work...).

Does that make sense? Basically the percentage difference between two numbers...
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
I'm sure this will be do-able, but to start off, let's just clarify the requirements exactly.

Please specify which results should be returned for each of these scenarios.

TARGET....FORECAST...RESULT
-5,000......+50,000.....+1000% ?
-5,000......-15,000.....-300% ?
-5,000......0................?
5,000.......+15,000.......?
5,000.......-15,000.......?
5,000.......0................?
0.............+15,000.......?
0.............-15,000.......?
0.............0................?
 

NikToo

New Member
Joined
Sep 24, 2015
Messages
48
Office Version
  1. 365
I'm sure this will be do-able, but to start off, let's just clarify the requirements exactly.

Please specify which results should be returned for each of these scenarios.

TARGET....FORECAST...RESULT
-5,000......+50,000.....+1000% ?
-5,000......-15,000.....-300% ?
-5,000......0................?
5,000.......+15,000.......?
5,000.......-15,000.......?
5,000.......0................?
0.............+15,000.......?
0.............-15,000.......?
0.............0................?

i'm seriously beginning to doubt it... I'm not sure how to handle the zero values. Same as if the target is -5000 and the forecast is -5500, how do I get it to show 90% (ie a positive number)? There's just too many permutations. Even this page says there's a problem doing it: https://www.excelcampus.com/functions/percentage-change-formula-negative-numbers/
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Just tell us what you think the results should be. If you can state that clearly, I'm fairly confident that I or someone else on this board can cope up with a formula solution.
By the way I'm not looking at your link.
 

NikToo

New Member
Joined
Sep 24, 2015
Messages
48
Office Version
  1. 365
Just tell us what you think the results should be. If you can state that clearly, I'm fairly confident that I or someone else on this board can cope up with a formula solution.
By the way I'm not looking at your link.

Say you have a target of -100, ie don't go below -100. So you only go to -90. Using normal methods, Excel will say it's 90%, but you're actually 10% better off, so it should be 110%. But if the result is -110, you're actually 90% of target, which makes -200 0% and -300 is -100%. I'm not sure how it would even figure out if the result is 0, is that 200%? So it just would make no sense to anyone looking at it.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
It sounds like you're giving up - fair enough.

If you change your mind, give us a full answer to post #2 and I'll see what I can do.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,398
Messages
5,601,440
Members
414,450
Latest member
Cassy_sn

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