Total percentage achieved for multiple targets

[parikshitpathak]

Hi All,

I am looking for a formula which will help me to find out the total %age achieved and %age deficit against the multiple targets. Example for Task A i have a target of 75% and for Task B i have a target of 25%. I want to find out how much is the total target I have achieved for each of the tasks and how much more i need to achieve in each of these tasks.

Can you help me to with the formula for this ? Thanks all in advance for the help



Thanks

Parikshit

[scottiex]

the amount achieved = "the percentage achieved" times "the target".
the total you want to achieve = "what you have achieved" divided by "the percentage achieved".

If you want something more complicated than that you probably need to give an example or be clearer in the explanation.

[parikshitpathak]

Hi Scottiex,

Thanks for the help.. What I am looking to achieve is mentioned below.

I have a target for 75% for one of the Task and 25% for another task. So how do i find what is the total %age of the targets I have achieved ? My total revenue target is 10,00,000 per month. For the Task A which is new sales my target is 75% of the Total Revenue target and for Task B which is Retention of the existing customers my target is 25%. So if I have achieved 5,00,000 for Task A and 1,00,000 for Task B, how can i find what is the total %age i have achieved across the tasks (Task A + Task B) and how much more do i need to achieve for each task (Task A and Task B) to meet the targets?

I hope I am able to explain it more clearly. Kindly help with this.

Thanks

Parikshit

[samba_ravi]

Pls attach a sample excel file with expected result with narration

[parikshitpathak]

Hi Siva

Please find attached excel sheet.

Thanks for the help in advance.

[samba_ravi]

B9=e3/b3
b10=e4/b4
b11=sum(e3:e4)/sum(b3:b4)
b12=sum(b3:b4)-sum(e3:e4)

[JohnTopley]

For Task A is it not

=E3/B3 i.e 60% ?

and similar for Task B (40%)

For A+B

=(E3+E4)/B2 (55%)

[parikshitpathak]

Many Thanks Siva and John for the help. I have one query here as i am confused about how the total deficit for combined task is calculated?
I have updated the excel sheet with some dummy figures and highlighted the query in Yellow color. Please help me to understand it.



Thanks to both of you for the quick help.. Awaiting your expert views.


Thanks

Parikshit

[kprasad]

Hi Parikshitpathak,

If you are tring to find the %age of deficit then check if below formula help you?

=((SUM(B3:B4)-SUM(E3:E4))/B2)*100

[samba_ravi]

30% is the Right Answer and change the formula as

=(B3+B4-E3-E4)/B2*100 Instead of =(B3+B4-E3+E4)/B2*100

[parikshitpathak]

Hi Siva and Prasad,

Thanks for the quick response. One last query, as per the excel sheet shared will the right answer for total deficit be as per the formula in
B12 cell or E11 cell ?


Thanks

Parikshit

[samba_ravi]

b12 as per my knowledge

[parikshitpathak]

Thanks Siva...

[kprasad]

With respect to shared sheet, change the formula in B12 as suggested by Siva or self. Secondly as per my knowledge as well the total deficit be as per formula in B12. Hope this clarify your query.

[parikshitpathak]

Thanks Prasad !! Appreciate the help from you and Siva.


Thanks

Parikshit

[JohnTopley]

See attached (with cells formatted as %)

[kprasad]

You are most welcome

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.