possible to calculate % attainment using one formula?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to measure one set of data over the other. For example, Set A is
forecast and Set B is actuals.
Set A 591 107 (160) (559) (159)
Set B 362 (24) 50 (121) (295)

First pair of data is simple. 362/591= 61%. This means acutals is 61% to
fcst.

Is there a formula to cater to all scenarios above?
 
What does the brackets indicate (negatives)? What answer do you expect from
this, a single ratio of the sums, multiple ratios,...?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Yes, brackets are negatives. I expect single ratio for each pair of data,
like my example. The tricky part is with the negatives.
 
So assuming it isn't a simple case of (24)/107, what is your calculation
rule.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Rule is the difference of POR and ACT divide by POR. I thought of 5
different formulas to cater each scenario. Easy way out is build mega formula
with IF condition. If this a better way of doing this???

=(ABS(D3)+ABS(D4))/ABS(D3)-1 (61% for first pair)
=-(ABS(E3)+ABS(E4))/ABS(E3)-1 (-222%)
=-(ABS(F3)+ABS(F4))/F3+1 (231%)
=(ABS(G3)+ABS(G4))/ABS(G3)-1 (22%)
=-(ABS(H3)+ABS(H4))/ABS(H3)+1 (-186%)


POR 591 107 (160) (559) (159)
ACT 362 (24) 50 (121) (295)
% perf 61% -222% 231% 22% -186%


Eddy
 
Well you seem to have tried to calculate a totally different parameter in
almost each case.

You say you wanted "the difference of POR and ACT divide by POR" but you
haven't got that.
If you look at your formulae again and do the algebra you'll see that the
parameter which you've divided by POR is not "the difference of POR and
ACT", but, in your 5 cases respectively:
ACT (so your formula is effectively =D4/D3)
ACT minus 2*POR (so your formula is =(E4 - 2*E3)/E3)
2*POR minus ACT (so your formula is =(2*F3 - F4)/F3)
ACT (so your formula is =G4/G3)
minus ACT (so your formula is =-H4/H3)

As you have worked out 4 different parameters across your 5 cases, are you
sure that you're calculating what you really want?

When you've decided what you're trying to calculate, then you can work out a
formula for it.
 
There is no rule there that you can apply that I can see. You take different
actions in different cases without any rule that I can see, and I don't get
the same answers as you in the formulae you use.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top