Forum Discussion
Balanced Scorecard Sliding Scale formula problem
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.
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 :)
So, what result would you expect for decreasing values? Can you explain the logic in words?
OMG! Thank you so much! this worked perfectly.
