Percent of Objective
Here's the scenario:
I have 5 areas of objectives, 2 measure in time, 2 in percent, and 1 in decimal. Going over objective in the Time and Decimal objectives is BAD, going over in the Percentage is GOOD! Example: You have a monthly blue widget objective (in time) 3:30 (3 hrs 30 min). At the end of the month you achieve 3:30, therefore you are at 100% of your objective. Lets say that Obj is in A3 and achieved is in A2; so A2/A3= 100%. Now lets say that the achieved is 4:10. If you use same formula of A2/A3 you have reached 119% of objective. However going OVER 3:30 is a bad thing in this example. The goal is to be 3:30 or lower. So, what I want to do is show only the % of obj that does not exceed 100%. If the answer exceeds 100% I want to show 0. However in the same file I have the opposite that if I exceed my objective, lets say 95%. and I get 97.5% then it is good and I would want to display the 102% The next step of this is that the results are then assigned a weighted factor toward an overall goal. So lets say that this objective carries a weight of 35%, and I achieved a blue widget result of 90% this would then account for approx 33% of my over all objectives(0.35*90%) So, I do not want to show % of obj for either time or decimal if I go over the objective. However with the objectives displayed as percentage I can display either above or below, in fact 110% of OBJ is a good thing! How do I write a formula that expresses a value no greater than 100% for some objectives but not all of them? I have seen this example, but it is not consistant =IF(1+(1-E56/F56)<0,0,1+(1-E56/F56)) As usual all help greatly appreciated |
I neglected to point out that achieveing less that 95% lets say 87%
achieved at end of month, would be equal to 91% of obj is ok to display. So for the obectives displayed as % it is ok to display results above & below 100%. However it it not ok to display over 100% for the time and decimal categories Sorry |
All times are GMT +1. The time now is 05:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com