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

OMG! Thank you so much! this worked perfectly.

Forum Discussion

Balanced Scorecard Sliding Scale formula problem

Resources