SOLVED
Home

Liner Interpolation Problem for Balanced Scorecard

%3CLINGO-SUB%20id%3D%22lingo-sub-309047%22%20slang%3D%22en-US%22%3ELiner%20Interpolation%20Problem%20for%20Balanced%20Scorecard%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309047%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20anyone%20assist%20my%20problem%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20balanced%20scorecard%20which%20I%20made%20and%20is%20almost%20perfect.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20following%20criteria%20are%20crucial%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3ELast%20Year%3C%2FSTRONG%3E%20-%20This%20is%2050%25%20of%20the%20allotted%20points%3C%2FP%3E%3CP%3E%3CSTRONG%3EPlan%26nbsp%3B%3C%2FSTRONG%3E-%20This%20is%2075%25%20of%20the%20allotted%20points%3C%2FP%3E%3CP%3E%3CSTRONG%3EStretch%3C%2FSTRONG%3E%20-%20This%20is%20100%25%20of%20the%20allotted%20points%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20If%20we%20look%20at%20%3CSTRONG%3EProfit%20Margin%2C%20%3C%2FSTRONG%3Eand%20the%20allotted%20point%20value%20is%20%3CU%3E35%3C%2FU%3E%20where%20an%20increase%20is%20what%20you%20want%2C%20my%20formula%20which%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%3CFONT%20color%3D%22%23000080%22%3EIngeborg%20Hawighorst%3C%2FFONT%3E%3C%2FA%3E%26nbsp%3Bmade%20for%20me%20works%20as%20seen%20below%3A%3C%2FP%3E%3CP%3E%3DIF(ytd%26gt%3B%3Dstretch%2Cweighted_value%2CIF(ytd%26gt%3B%3Dplan%2C((weighted_value-((weighted_value%2Fytd)*plan))%2Bweighted_value)*0.75%2C%20IF(ytd%26gt%3B%3Dlast_year%2C((35-((35%2Fytd)*last_year))%2B35)*0.5)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20for%20Safety%20Accidents%2C%20Less%20is%20better%20and%20doesn't%20seem%20to%20calculate%20properly.%20Im%20thinking%20it%20is%20a%20BEDMAS%20issue%20maybe%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESafety%20Accidents%20Data%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ELast%20year%20200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Plan%20150%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Stretch%20100%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESafety%20Accidents%20(Lets%20us%2035%20as%20the%20allotted%20points%20for%20example)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ELast%20year%20200%20accidents.%20In%20the%20formula%2C%20this%20should%20reflect%26nbsp%3B17.5%20out%20of%2035%20points%3C%2FP%3E%3CP%3EPlan%20150%26nbsp%3Baccidents.%20In%20the%20formula%2C%20this%20should%20reflect%2026.25%20points%3C%2FP%3E%3CP%3EStretch%20100%20accidents.%20In%20the%20formula%2C%20this%20should%20reflect%2035%20points%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20above%20seen%20in%20the%20%3CSTRONG%3EProfit%20Margin%3C%2FSTRONG%3E%20does%20not%20work%20if%20the%20values%20are%20decreasing.%20It%20may%20be%20an%20order%20of%20operations%20error%20because%20the%20values%20are%20decreasing%20not%20increasing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20the%20following%20formula%20which%20is%20EXTREMELY%20close%20to%20working%3A%3C%2FP%3E%3CP%3E%3DIF(YTD%26gt%3B%3DLast_Year%2C0.5*Weighted_Value%2CIF(YTD%26lt%3B%3DStretch%2CWeighted_Value%2C((Weighted_Value-0.5*Weighted_Value)%2F(Last_Year-Stretch))*(YTD-100)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThese%20are%20the%20values%20I%20get%20with%20the%20above%20formula%20if%20I%20enter%20the%20following%20data%20in%20the%20YTD%20cell%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3ESafety%20Accidents%20(Lets%20us%2035%20as%20the%20allotted%20points%20for%20example)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E(Last%20year%20%3D%20500.%20Plan%20%3D%20400.%20Stretch%20%3D%20300)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E500%20accidents.%20In%20the%20formula%2C%2017.5%20out%20of%2035%20points%20(Correct)%3C%2FP%3E%3CP%3E450%20Accidents.%20In%20the%20formula%2C%2030.63%20out%20of%2035%20points%20%3CSTRONG%3E(Incorrect)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E400%26nbsp%3Baccidents.%20In%20the%20formula%2C%2026.25%20out%20of%2035%20points%20(Correct)%3C%2FP%3E%3CP%3E350%20Accidents.%20In%20the%20formula%2C%26nbsp%3B21.88%20out%20of%2035%20points%20%3CSTRONG%3E(Incorrect)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E300%20accidents.%20In%20the%20formula%2C%26nbsp%3B35%20out%20of%2035%20points%20(Correct)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-309047%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482300%22%20slang%3D%22en-US%22%3ERe%3A%20Liner%20Interpolation%20Problem%20for%20Balanced%20Scorecard%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482300%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F249832%22%20target%3D%22_blank%22%3E%40Will_Sarty01%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481936%22%20slang%3D%22en-US%22%3ERe%3A%20Liner%20Interpolation%20Problem%20for%20Balanced%20Scorecard%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481936%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20solved%20everything!%20Thank%20you%20so%20much!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481648%22%20slang%3D%22en-US%22%3ERe%3A%20Liner%20Interpolation%20Problem%20for%20Balanced%20Scorecard%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481648%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F249832%22%20target%3D%22_blank%22%3E%40Will_Sarty01%3C%2FA%3E%26nbsp%3B%2C%20if%20you%20mean%20this%20thread%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FBalanced-Scorecard-Sliding-Scale-formula-problem%2Fm-p%2F295449%23M22540%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FBalanced-Scorecard-Sliding-Scale-formula-problem%2Fm-p%2F295449%23M22540%3C%2FA%3E%26nbsp%3Bwhere%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%20answered%2C%20I%20guess%20you%20shall%20just%20revert%20your%20formula%20in%20opposite%20direction%2C%20like%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3DIF(ytd%26lt%3B%3Dstretch%2C%0A%20%20%2035%2C%0A%20%20%20IF(ytd%26lt%3B%3Dplan%2C%0A%20%20%20%20%20%2026.25%2B(plan-ytd)%2F(plan-stretch)*(35-26.25)%2C%0A%20%20%20%20%20%20IF(ytd%26lt%3B%3Dlast_year%2C%0A%20%20%20%20%20%20%20%20%2017.5%2B(last_year-ytd)%2F(last_year-plan)*(26.25-17.5)%2C%0A%20%20%20%20%20%20%20%20%2017.5)%0A%20%20%20)%0A)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481636%22%20slang%3D%22en-US%22%3ERe%3A%20Liner%20Interpolation%20Problem%20for%20Balanced%20Scorecard%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481636%22%20slang%3D%22en-US%22%3EThank%20you%20for%20your%20response.%20I%20will%20try%20to%20upload%20tomorrow.%20I%20wasn%E2%80%99t%20aware%20I%20could%20add%20a%20file%20in%20here%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481607%22%20slang%3D%22en-US%22%3ERe%3A%20Liner%20Interpolation%20Problem%20for%20Balanced%20Scorecard%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481607%22%20slang%3D%22en-US%22%3EPerhaps%2C%20no%20one%20can%20imagine%20such%20a%20complex%20situation%20without%20attaching%20your%20sample%20file.%20The%20absence%20of%20your%20sample%20file%20also%20explains%20why%20you%20received%20no%20conceivable%20response%20until%20now.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481177%22%20slang%3D%22en-US%22%3ERe%3A%20Liner%20Interpolation%20Problem%20for%20Balanced%20Scorecard%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481177%22%20slang%3D%22en-US%22%3E%3CP%3EWould%20like%20to%20finally%20complete%20this.%20Can%20anyone%20assist%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Will_Sarty01
Occasional Contributor

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)

6 Replies

Would like to finally complete this. Can anyone assist?

Highlighted
Perhaps, no one can imagine such a complex situation without attaching your sample file. The absence of your sample file also explains why you received no conceivable response until now.
Thank you for your response. I will try to upload tomorrow. I wasn’t aware I could add a file in here
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)
   )
)

 

@Sergei Baklan 

 

this solved everything! Thank you so much!!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies