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?
Calculate percentage of target achieved when target is a negative number
- Thread starter Kamarlon
- Start date
- Status
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
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?
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.
=(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.
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.
- Status