Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Formula or Function to calculate Margin (reverse of Percent a | Excel Worksheet Functions | |||
percent frequency | New Users to Excel | |||
Is there a way to have two values (percent and number) in a label. | Charts and Charting in Excel | |||
calculate percent increase | Excel Discussion (Misc queries) | |||
calculate percent from multiple criteria | Excel Worksheet Functions |