Forum Discussion

Copper Contributor

Dec 05, 2018

Solved

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 sh...

Excell

IngeborgHawighorst
Dec 05, 2018
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.

IngeborgHawighorst

MVP

Dec 05, 2018

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.

Will_Sarty01

Copper Contributor

Dec 06, 2018

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

Will_Sarty01
Copper Contributor
Dec 10, 2018
Still looking for assistance on this formula. If anyone can help, it would be greatly appreciated.

Thanks :)
- IngeborgHawighorst
  MVP
  Dec 11, 2018
  So, what result would you expect for decreasing values? Can you explain the logic in words?
  - Will_Sarty01
    Copper Contributor
    Dec 12, 2018
    The final value should be the same. If I reach the Stretch, I get 100% of the allotted points, if I reach Goal, I get 75% of the allotted points, and if I het Last Year, I get 50% of the allotted points.
    
    So, If we look at Profit Margin where an increase is what you want, your formula works as seen below:
    =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)))
    
    However, for Safety Accidents, Less is better and doesn't seem to calculate properly. Im thinking it is a BEDMAS issue maybe?
    
    Safety Accidents (Lets us 35 as the allotted points for example)
    Last year 200 accidents. In the formula, this should reflect 35 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 17.5 points