% achieved to a negative goal

[Analyst_John]

I am working with a data set that I have to calculate the % to goal achieved.
For this data set though, some of the goals are negaitve growth goals. So
for example, if the goal is -10,000 in growth, meaning they expect the
balance to shrink, if they are actually -10,000 they have achieved 100%. If
they lose more than that, they start going below 100% and if they don't
shrink that much or actually grow the balance, it would be above 100%
achieved. This get's weird with the percentages because it calculates that
if the goal is -10,000 and they stay even, (0), that it is 10,000%, but the
way I need it to show is that they were at 200% of goal. So being at -20,000
would be at 0% of goal, and below -20,000 in growth would be a -%. Any help
you can give would be great.

Thanks.

 

[Fred Smith]

I would set all the goals as positive numbers.

A goal of -10,000 growth is also a goal of 10,000 decrease.

Once you have all the goals as positive numbers, calculating the % achieved is
straightforward.

 

[Analyst_John]

Unfortunately I can't do that though because if they do have positive growth

 

[Fred Smith]

It wouldn't show as an "incorrect percentage" if you had the correct formula. So
the issue is devising the correct formula, where "correct" is your definition of
correct.

So tell us what you want to achieve. Give us examples, the formula you are using
and where it produces incorrect results. It won't take much effort to get the
right formula.

 

[Harlan Grove]

I am working with a data set that I have to calculate the % to goal
achieved. For this data set though, some of the goals are negaitve
growth goals. So for example, if the goal is -10,000 in growth,
meaning they expect the balance to shrink, if they are actually
-10,000 they have achieved 100%. If they lose more than that, they
start going below 100% and if they don't shrink that much or
actually grow the balance, it would be above 100% achieved.

Wrong. If the goal is 10,000 reduction, then express reduction as a
positive quantity, and calculate percentages as usual. If growth
occurs rather than reduction, then that's NEGATIVE REDUCTION. Also, if
reduction EXCEEDS 10,000, then the percentage of the goal INCREASES
PAST 100%. So with 10,000 reduction as the goal,

6,500 reduction -> 65% of goal
11,000 reduction -> 110% of goal
2,400 GROWTH -> -24% of goal.

This is no different than if the goal were 10,000 growth but 2,400
reduction occurred - the percentage of the goal would be -24%.

This get's weird with the percentages because it calculates that
if the goal is -10,000 and they stay even, (0), that it is 10,000%,
but the way I need it to show is that they were at 200% of goal. So
being at -20,000 would be at 0% of goal, and below -20,000 in growth
would be a -%. . . .

Ending up at 0 with a -10,000 goal would normally be 0%, not 10,000%
using the standard definition of percent.

Looks like you want a simple linear calculation of something
masquerading as a percentage. Try

=2-achieved/goal

and format as 0.00%.

 

[joeu2004]

if the goal is -10,000 in growth, [....] if they are actually -10,000
they have achieved 100%. If they lose more than that, they start
going below 100% and if they don't shrink that much or actually
grow the balance, it would be above 100% achieved. This get's
weird with the percentages because it calculates that if the goal
is -10,000 and they stay even, (0), that it is 10,000%, but the way
I need it to show is that they were at 200% of goal. So being at
-20,000 would be at 0% of goal, and below -20,000 in growth would
be a -%.

I am not saying that I agree objectives. But it sounds like you want
the following results, for example (A = actual, G = goal):

If A = G - 2*abs(G), then 100%
if A = G - abs(G), then 0%
if A = G , then 100%
if A = G + abs(G), then 200%
if A = G + 2*abs(G), then 300%

If I am correct about that, I think the following formula achieves
that (B1 = actual, B2 = goal):

=if(n(B2)=0, "", 1 + (B1-B2) / abs(B2))

That seems to work for both positive and negative goals, but not if
the goal is zero. But do experiment to be sure that gives you the
results you want. If not, please post back with some counter-
examples.

Note: The for n(B2) guards against both zero and blank values for B2
(goal).

HTH.

 

[Analyst_John]

This actually is exacly what I was looking for. I know logically it may not
be sound, but it's how they want the results to look. Thanks so much!

if the goal is -10,000 in growth, [....] if they are actually -10,000
they have achieved 100%. If they lose more than that, they start
going below 100% and if they don't shrink that much or actually
grow the balance, it would be above 100% achieved. This get's
weird with the percentages because it calculates that if the goal
is -10,000 and they stay even, (0), that it is 10,000%, but the way
I need it to show is that they were at 200% of goal. So being at
-20,000 would be at 0% of goal, and below -20,000 in growth would
be a -%.

I am not saying that I agree objectives. But it sounds like you want
the following results, for example (A = actual, G = goal):

If A = G - 2*abs(G), then 100%
if A = G - abs(G), then 0%
if A = G , then 100%
if A = G + abs(G), then 200%
if A = G + 2*abs(G), then 300%

If I am correct about that, I think the following formula achieves
that (B1 = actual, B2 = goal):

=if(n(B2)=0, "", 1 + (B1-B2) / abs(B2))

That seems to work for both positive and negative goals, but not if
the goal is zero. But do experiment to be sure that gives you the
results you want. If not, please post back with some counter-
examples.

Note: The for n(B2) guards against both zero and blank values for B2
(goal).

HTH.