T
TesnoBay
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 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