Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
possible to calculate % attainment using one formula?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
possible to calculate % attainment using one formula?
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) "Eddy" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
possible to calculate % attainment using one formula?
Yes, brackets are negatives. I expect single ratio for each pair of data,
like my example. The tricky part is with the negatives. "Bob Phillips" wrote: 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) "Eddy" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
possible to calculate % attainment using one formula?
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) "Eddy" wrote in message ... Yes, brackets are negatives. I expect single ratio for each pair of data, like my example. The tricky part is with the negatives. "Bob Phillips" wrote: 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) "Eddy" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
possible to calculate % attainment using one formula?
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 "Bob Phillips" wrote: 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) "Eddy" wrote in message ... Yes, brackets are negatives. I expect single ratio for each pair of data, like my example. The tricky part is with the negatives. "Bob Phillips" wrote: 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) "Eddy" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
possible to calculate % attainment using one formula?
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. -- David Biddulph "Eddy" wrote in message ... 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 "Bob Phillips" wrote: 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) "Eddy" wrote in message ... Yes, brackets are negatives. I expect single ratio for each pair of data, like my example. The tricky part is with the negatives. "Bob Phillips" wrote: 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) "Eddy" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
possible to calculate % attainment using one formula?
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) "Eddy" wrote in message ... 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 "Bob Phillips" wrote: 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) "Eddy" wrote in message ... Yes, brackets are negatives. I expect single ratio for each pair of data, like my example. The tricky part is with the negatives. "Bob Phillips" wrote: 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) "Eddy" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to calculate 401(k) | Excel Worksheet Functions | |||
Formula won't calculate | Excel Discussion (Misc queries) | |||
Calculate formula only once. | Excel Discussion (Misc queries) | |||
How do I calculate a formula once | Excel Worksheet Functions | |||
Formula to Calculate Age | Excel Worksheet Functions |