% achieved to a negative goal
On Nov 30, 11:12 am, Analyst_John
wrote:
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.
|