Forum Discussion
Balanced Scorecard Sliding Scale formula problem
I developed a balanced scorecard for my company. I have the formula almost working but its not 100%
Context -
If at Last year amount, value given should be 50% of total weight
If at Plan, it should be 75% of the weight.
If at Stretch, it should reflect 100% of the weighted points.
My current formula. (This one has a weight of 35 allowable points)
I29 is Year to date. H29 is the stretch. G29 is the Plan. F29 is last year
=IF(I29>=H29,35,IF(I29>=G29,35*0.75, IF(I29>=F29,35*0.5)))
Problem, it works perfectly if I reach the exact target amount. example:
$100 is last year (should be valued at 17.5 of 35 points)
$200 is Plan (should be valued at 26.25 of 35 points)
$300 is Stretch (should be 35 of 35 points)
If I enter $299 in the YTD cell, it spits out 26.25. It does steadily gain value. Really, is should be almost 34 of 35 points.
Hello,
your formula only has three levels and you hard code 35*0.75. There is nothing sliding here.
What calculation do you apply to arrive at "almost 35" for 299? Can you explain the logic in words?
It looks like you want to base the calculation on the ytd value as a percentage of the last year or plan value. So you may want to use
=IF(ytd>=stretch,35,IF(ytd>=plan,((35-((35/ytd)*plan))+35)*0.75, IF(ytd>=last_year,((35-((35/ytd)*last_year))+35)*0.5)))
Let me know if that helps.
8 Replies
Hello,
your formula only has three levels and you hard code 35*0.75. There is nothing sliding here.
What calculation do you apply to arrive at "almost 35" for 299? Can you explain the logic in words?
It looks like you want to base the calculation on the ytd value as a percentage of the last year or plan value. So you may want to use
=IF(ytd>=stretch,35,IF(ytd>=plan,((35-((35/ytd)*plan))+35)*0.75, IF(ytd>=last_year,((35-((35/ytd)*last_year))+35)*0.5)))
Let me know if that helps.
I ran into one snag. The formula works perfectly for items that are increasing but not decreasing
Example:
Profit Margin
Last year $100 Plan $200 Stretch $300 = formula works
Safety Accidents
Last year 200 Plan 150 Stretch 100 = formula doesn't work
Still looking for assistance on this formula. If anyone can help, it would be greatly appreciated.
Thanks :)
OMG! Thank you so much! this worked perfectly.
