• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate percentage of target achieved when target is a negative number

Status
Not open for further replies.

Kamarlon

New Member
In my dashboard I report on percentage of target achieved. Its calculated as (actual/target)*100. My problem is that some of the targets are actually negative. So if you had target of -2 and the actual was 4 the calculation will be -200%. This is incorrect as it should be -300%. I plan on using teh absolute value of the percentage. Has anyone encountered this problem and how did you use excel to solve it?
 
You should be using

=(Target-Actual)/Target

Formatted as Percentage
 
Formula looks to be pretty self-explanatory

Plugging in your example numbers, this would be:

=(-2-4)/-2

=-6/-2

=3


Formatted as a percentage, this would be 300%. If this should have been -300%, switch Hui's formula around to:

=(Actual-Target)/Target


Which would then be:

=[4-(-2)]/-2

=[6]/-2

=-3
 
Here is my concern with this calculation. Say target is 4 and you get 4. Using this formula you would have (4-4)/4 = 0


I have 2 other challenges:


1. What if the target is 0? Excel shows error as is expected. How do you guys get around this. The KPI is number of accidents. Should I reword it to say % compliance with accident regulation and set the target to 100%?


2. How do you handle KPIs such as expenditure where getting an actual figure which is higher than target is actually a bad thing?
 
If you are calculating percentage of target achieved, then the target has to be larger than the actual number and both should be positive. For this the formula is:

=(Actual/Target)*100


If you have negative numbers, you can only calculate percentage change from actual to target. For this a formula would be:

=IF(Target>Actual,Actual/Target,(Target-Actual)/Actual)


There is something you can do when numbers are negative but you need to set up a base value and calculate percentages based on how far target and actual values are from the base.
 
Based on your original post, I would have assumed that being on target is = to 0. Ie, actual of 4 was -300%, an actual of 2 would be -200%, actual of 0 would be -100%, so actual of -2 would be 0%.

If you're wanting 100% to be = to target, then your original formula is correct, and -200% was the correct display. (if -2 is target, then each a change in 1 is worth 50%)


1. If target is 0, then your percentage based on previous would be actual * 100%. Maybe something like:

=IF(Target=0,Actual,(Target-Actual)/Target)

format as percentage


2. I usually have a column that I use for goal listing, such as ">80%", "<100%", etc.
 
Thanks for the feedback. I'll make adjustenmnst accordingly.


Luke M. Can you expound more on the column that you include for goal listing?
 
Hi,
What if my target is less than 0.24 and my actual performance is 1.4. Now how do i calculate the %
 
Status
Not open for further replies.
Back
Top