• 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.

How to calculate goal percent achieved when a lower value is better?

M

michael73

New Member
I have a goal where I want to calculate the % achieved where goal target can vary and we want to be able to do relative comparisons. The tricky part is the lower the actual performance the better. So say the goal is for cycle time and the target is 5. If my actual cycle time is 3 I did better than 100% if actual cycle time is 8 I did less than 100% of goal. Any thoughts on a formula that could handle following exmaples to calculate % achieved:


Goal Actual

5 0

5 3

5 30
 
Interesting...


One way to look at this is to divide them by 1.


For ex.: the % completion for 5 & 3 would be,


(1/3) / (1/5) = 166.7%
 
Thanks, this seems to do the trick. Only issue is in the case of 0. For this goal I can't rule out that the actual value could be 0. Any way to handle this?
 
michael73,


You can add an IF() statement like this:


Code: 
=IF(B2=0,"-",A2/B2)
where A2 contains target value and B2 Contains actual value. This will show a dash (-) if encounters zero as actual.


Regards,

Faseeh
 
Faseeh - I don't think that answers my question. If my goal is a cycle time of 5 and my actual cycle time was 0 (just assume that's possible), and the lower the cycle time the better, what is my % of goal achieved. Chandoo's formula of (1/Actual)/(1/Goal) works except for the scenario when the actual is 0.
 
Hi michael73


See this:

[pre]
Code: 
5	5	100%    You hit you target
5	4	125%    You do it 1.25 times faster
5	3	167%    You do it 1.67 times faster
5	2	250%    You do it 2.50 times faster
5	1	500%    You do it 5.00 times faster
5	0	#DIV/0! Thing was already done, Is there any need for target??
[/pre]
..my formula follows this analogy, does that makes sense?? :)


Regards,
 
Faseeh,


Thanks for the feedback. Let me try another example to explain. Say you track defects. The goal is to have 5 defects or less. What % of goal did I achieve if I have 0 defects?

The 0 case is necessary so all targets can be compared relatively.


(the formula Chandoo provided works perfect except for this one case)
 
From a mathematics standpoint, you would have achieved infinite %. From Farseeh's post, you can see that as the defects decrease, percentage increases. This is more easily seen if we arrange Chandoo's formula to be:

=(1/Actual)/(1/Goal)

=(1/Actual)*(Goal/1)

=Goal/Actual


Anytime you have a denominator getting smaller and smaller, the result approaches infinite.


So, for your workbook, you'll need to use an error trapping formula like:

=IF(Actual=0,"Absolute perfection",Goal/Actual)
 
Luke,


Not sure I follow your logic. The goal is not 0 the goal is 5. In this scenario 0 doesn't hold any special significance except that I did 5 "better" than the goal of 5.
 
Correct. I think what's causing the "oddness" about the problem is that you're using a percentage. As I said, you're approaching infinity as the denominator gets smaller. You can call that whatever you want in terms of error catching. Perhaps "N/A" would be better?


Image at top of this site:

http://en.wikipedia.org/wiki/Divide_by_0


Shows graphically how your percentage is increasing at the 0 point.
 
Hi michael73,


Extending your example of defects, what would 167% efficient process means?? That would be meaning less, because your product could be 100% free of defect, not more then that? Is that correct?


Take this example of defects is it correct:

[pre]
Code: 
Target	Actual	%age Eff	Remarks
5	5	0%	        Your Process is 0% Efficient ( 5 /5 Defects Passed out)
5	4	20%	        Your Process is 20% Efficient ( 4 /5 Defects Passed out)
5	3	40%	        Your Process is 40% Efficient ( 3 /5 Defects Passed out)
5	2	60%	        Your Process is 60% Efficient ( 2 /5 Defects Passed out)
5	1	80%	        Your Process is 80% Efficient ( 1 /5 Defects Passed out)
5	0	100%	        Your Process is 100% Efficient ( 0 /5 Defects Passed out)
[/pre]
Regards,
 
Hi, michael73!


If you define success as goal achievement, the formula should be:

=SI(B2<=A2;1;(A2-B2)/A2) -----> in english: =IF(B2<=A2,1,(A2-B2)/A2)


If not, please define mathematically what you tried to mean by "% achieved". Otherwise this is rather a pun than an Excel issue.


Regards!
 
My situation is a bit different and I hope someone can let me know if and how to make this happen. My goal is zero and I need to calculate the percengtage to goal when my quantity eceeds that goal:


Goal=0 Actual=13

Goal=0 Actual=2


Any help would be great, thanks!!
 
@anasmedic

Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

Perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).

Regards!

PS: Please don't answer here at this thread.
 
You must log in or register to reply here.
Back
Top