How to handle percentage to goal with 0 and a negative number

[Mikeflash]

Hello!

In my day to day job, we have goals set (for each month) that can be either positive or negative "net adds", and I can reach negative or positive actual net adds by the end of the month. I am having issues getting excel to populate my percent to goal under the following parameters:

My net adds goal is -54
My net adds actual is 0

My net adds number could have been positive or negative, at the end of the month, but it ended up at 0.

Can someone please help me figure out how to get the percentage to goal for the above? Any assistance would be greatly appreciated!

Thanks,

Mike

 

[Glove_Man]

Hello!


Can someone please help me figure out how to get the percentage to goal for the above? Any assistance would be greatly appreciated!

Thanks,

Mike

You're really asking something which is more about a mathematics question than an excel one...

But the ABS() function could be useful

Something like = ABS(current - target) / ABS (target - start) should get you pretty close

Added: Actually, the ABS function will be a little tricky, if you can get positive numbers as well. Like if you start at 0, go to +2, with a target of -54. You're now at -4% completed... Let me think about this.

 

[Glove_Man]

OK

Mathematically, you will need to use the ABS() function to find absolute values sometimes.

For your above example, except that your current net adds is +2
The goal is -54.

ABS(Goal) = 54
Remaining to be completed: Goal - current = -54 - 2 = -56.
ABS(Rem To Be Completed) = ABS(-56) = 56

So proportion remaining = (ABS(Rem to be completed) / ABS (target)) = 56/54 = 104%

Proportion completed = 100% - Proportion remaining = -4%

That should work

 

[Mikeflash]

Appreciate your trying to help, Glove_man!

 

[Mikeflash]

Appreciate the help! I'll see if I can make this work! I neglected to include a little more information. Here is how my number pan out:
Connects goal for the month: 300
Disconnects goal for the month: 354
This totals out to my goal of -54 net connects

Actual connects at the end of the month: 300
Actual Disconnects at the end of the month: 300
This totals out to my actual net connects of 0

Thanks again for everything, and I appreciate the explanations of ABS. I'll see what I can get going!

 

[DSCfromCFA]

G'day Mike,

you could do a simple excess/deficit percentage calculation using

=-(1-(Actual/Goal)) (Format the cell to %)

for both connects and disconnects.

If you want to aggregate to a single excess/deficit percentage then that is ok too, but doesn't make a whole heap of sense (based on the information given).

Cheers

shane

 

[Mikeflash]

I appreciate the response also! I’ll give this a try too! I got the equation to work from what Glove_man gave me, but maybe this will be a little easier to explain to my superiors. Either way, thanks to both of you for your responses!

 

[DSCfromCFA]

You're welcome, and good luck.