Jan 04 2019 08:45 AM
Can anyone assist my problem?
I have a balanced scorecard which I made and is almost perfect.
The following criteria are crucial:
Last Year - This is 50% of the allotted points
Plan - This is 75% of the allotted points
Stretch - This is 100% of the allotted points
So, If we look at Profit Margin, and the allotted point value is 35 where an increase is what you want, my formula which Ingeborg Hawighorst made for me works as seen below:
=IF(ytd>=stretch,weighted_value,IF(ytd>=plan,((weighted_value-((weighted_value/ytd)*plan))+weighted_value)*0.75, IF(ytd>=last_year,((35-((35/ytd)*last_year))+35)*0.5)))
However, for Safety Accidents, Less is better and doesn't seem to calculate properly. Im thinking it is a BEDMAS issue maybe?
Safety Accidents Data
Last year 200 Plan 150 Stretch 100
Safety Accidents (Lets us 35 as the allotted points for example)
Last year 200 accidents. In the formula, this should reflect 17.5 out of 35 points
Plan 150 accidents. In the formula, this should reflect 26.25 points
Stretch 100 accidents. In the formula, this should reflect 35 points
The formula above seen in the Profit Margin does not work if the values are decreasing. It may be an order of operations error because the values are decreasing not increasing.
I have tried the following formula which is EXTREMELY close to working:
=IF(YTD>=Last_Year,0.5*Weighted_Value,IF(YTD<=Stretch,Weighted_Value,((Weighted_Value-0.5*Weighted_Value)/(Last_Year-Stretch))*(YTD-100)))
These are the values I get with the above formula if I enter the following data in the YTD cell:
Safety Accidents (Lets us 35 as the allotted points for example)
(Last year = 500. Plan = 400. Stretch = 300)
500 accidents. In the formula, 17.5 out of 35 points (Correct)
450 Accidents. In the formula, 30.63 out of 35 points (Incorrect)
400 accidents. In the formula, 26.25 out of 35 points (Correct)
350 Accidents. In the formula, 21.88 out of 35 points (Incorrect)
300 accidents. In the formula, 35 out of 35 points (Correct)
Apr 23 2019 06:33 AM - edited Apr 23 2019 06:34 AM
Would like to finally complete this. Can anyone assist?
Apr 23 2019 03:05 PM
Apr 23 2019 03:42 PM
Apr 23 2019 04:06 PM
Solution@Will_Sarty01 , if you mean this thread https://techcommunity.microsoft.com/t5/Excel/Balanced-Scorecard-Sliding-Scale-formula-problem/m-p/29... where @Ingeborg Hawighorst answered, I guess you shall just revert your formula in opposite direction, like
=IF(ytd<=stretch, 35, IF(ytd<=plan, 26.25+(plan-ytd)/(plan-stretch)*(35-26.25), IF(ytd<=last_year, 17.5+(last_year-ytd)/(last_year-plan)*(26.25-17.5), 17.5) ) )
Apr 24 2019 05:38 AM
Apr 24 2019 01:56 PM
@Will_Sarty01 , you are welcome
Apr 23 2019 04:06 PM
Solution@Will_Sarty01 , if you mean this thread https://techcommunity.microsoft.com/t5/Excel/Balanced-Scorecard-Sliding-Scale-formula-problem/m-p/29... where @Ingeborg Hawighorst answered, I guess you shall just revert your formula in opposite direction, like
=IF(ytd<=stretch, 35, IF(ytd<=plan, 26.25+(plan-ytd)/(plan-stretch)*(35-26.25), IF(ytd<=last_year, 17.5+(last_year-ytd)/(last_year-plan)*(26.25-17.5), 17.5) ) )