Figuring out pecentage differences with negative and postive numbers

[NikToo]

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

 

[Gerald Higgins]

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]

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]

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]

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]

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.