- 546K Members
- 2,644 Online
- 652K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Balanced Scorecard Sliding Scale formula problem

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
## Balanced Scorecard Sliding Scale formula problem

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2018 09:01 AM

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.

Labels:

8 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2018 12:01 PM

SolutionHello,

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.

Best Response confirmed by
Will_Sarty01 (Occasional Contributor)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2018 09:39 AM

OMG! Thank you so much! this worked perfectly.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2018 11:22 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018 03:39 AM

Still looking for assistance on this formula. If anyone can help, it would be greatly appreciated.

Thanks :)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-11-2018 01:07 PM

So, what result would you expect for decreasing values? Can you explain the logic in words?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-12-2018 03:45 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-20-2018 07:04 AM - edited 12-20-2018 07:25 AM

Hope all is well Ingeborg. I thank you for your help thus far. Do you have any questions regarding my last explanation?

Thanks again!

- Tags:
- Ingeborg Hawighorst

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-02-2019 04:40 AM - edited 01-02-2019 05:08 AM

Can anyone assist with the above problem? I hope I was able to explain it properly.

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

Related Conversations

Report for Videoconferencing quality

Thomas_Steibl
in
Microsoft Teams
on
02-21-2020
113
Views

0 Likes

3 Replies

Cortana is having problem doing basic conversation Version: 2.2002.4747.0

HotCakeX
in
Discussions
on
02-20-2020
35
Views

0 Likes

0 Replies

How to set my VPN connection as Non-metered in Windows 10?

HotCakeX
in
Windows 10
on
02-20-2020
28
Views

0 Likes

0 Replies

The option to apply blur effect to all screens is volatile and keep resetting

HotCakeX
in
Report an Issue
on
02-20-2020
51
Views

0 Likes

2 Replies

The old extension store website need to be replaced

HotCakeX
in
Discussions
on
02-19-2020
146
Views

0 Likes

10 Replies

Windows Store Library shows empty list in "All owned" section when the type Is set to "All Types"

HotCakeX
in
Windows 10
on
02-14-2020
62
Views

1 Likes

0 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft