Goal Seek Help

[ChrisP1]

Hi Guys

Love the forum and used its answers anonymously many times, though today Ive come across a problem I cant find a solution too:

</SPAN>I need to know how to identify the number of required tickets to achieve / fail a Service Level Agreement (SLA) to help manage resource.

For example, if we are over achieving in one area, but failing in another, I would like to know how many tickets would it take to fail SLA on the over achieving area, and how many tickets would it take to achieve the under performing SLA.

I have an example table of data counting the number of tickets resolved within SLA and Outside SLA for 6 different Ticket Priorities. Each Priorityhas a target, some are over performing, some are under performing.

Total equals Sum of Within SLA and Breached SLA
% Within SLA equals Within SLA / Total (as percentage)

Ideally, I would like a plus or minus figure listed in the last column for each of the 6 values.

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Priority[/TD]
[TD]Target[/TD]
[TD]Within SLA [/TD]
[TD]Breached SLA[/TD]
[TD]Total[/TD]
[TD]% Within SLA[/TD]
[TD]Required to Fail / Achieve Target[/TD]
[/TR]
[TR]
[TD]P1[/TD]
[TD]90%[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]87.50%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD]90%[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P3[/TD]
[TD]85%[/TD]
[TD]28[/TD]
[TD]5[/TD]
[TD]33[/TD]
[TD]84.85%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P4[/TD]
[TD]85%[/TD]
[TD]198[/TD]
[TD]11[/TD]
[TD]209[/TD]
[TD]97.74%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P9[/TD]
[TD]60%[/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD]9[/TD]
[TD]77.78%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VIP[/TD]
[TD]95%[/TD]
[TD]22[/TD]
[TD]1[/TD]
[TD]23[/TD]
[TD]95.65%[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

I have tried to use Goal Seek to identify this value, but Goal Seek not only overwrites my original figures in Within SLA field, it also only appears to work for providing a figure for a failing target. It doesnt work for calculating a value for how many I can allow to breach before failling a target.

Does anyone have any ideas?

Thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

Can't you just use a formula? What would the results be for your sample data?

 

[ChrisP1]

Hi

Apologies, a plus or minus value in the last column wouldnt work, we simply need to identify what has achieved target and what has failed. Once we know that (which we do), the next calculation is to see how many additional tickets are required to achieve the target, or fail the target depending on the result of the 1st calculation

This is where I have a problem, the issue seems to be that the increase in either the Within SLA value or the Breached SLA value also increase the total. So i'll never know what the total is unti l Ive worked out the additional number of Within SLA tickets or Breached SLA tickets required, but this is the value I'm trying to calculate in the first place....

Using a manual calculation to achieve the P1 target of 90%, we would need an additional 12 tickets to be resolved within SLA...which would make the % Within Caluclation (19/20)*100 = 95%

The results for the remaining targets (without factoring in the potential increase on the would be:

P1: + 12 Within SLA Required to achieve target
P2: + 1 Breached SLA until failure
P3: + 17 Within SLA Required to achieve target
P4: + 24 Breached SLA until failure
P9: + 9 Breached SLA until failure
VIP: +1 Breached SLA until failure

Thanks

 

[Juneau07]

Hi,

Can you help me in identifying this? I need to find out the formula on what should be the minimum score to ensure that the target is met with a specified number of surveys.

[TABLE="width: 652"]
<tbody>[TR]
[TD]Manager[/TD]
[TD]Count Surveys[/TD]
[TD]% Score[/TD]
[TD]Target Survey Count[/TD]
[TD]Target[/TD]
[TD]Surveys Needed[/TD]
[TD]Min % Needed[/TD]
[/TR]
[TR]
[TD]Manager A[/TD]
[TD]12[/TD]
[TD]95.00%[/TD]
[TD]108[/TD]
[TD]93.50%[/TD]
[TD]96[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Manager B[/TD]
[TD]10[/TD]
[TD]96.80%[/TD]
[TD]110[/TD]
[TD]93.50%[/TD]
[TD]100[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Manager C[/TD]
[TD]12[/TD]
[TD]89.50%[/TD]
[TD]154[/TD]
[TD]93.50%[/TD]
[TD]142[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Manager D[/TD]
[TD]18[/TD]
[TD]91.00%[/TD]
[TD]212[/TD]
[TD]93.50%[/TD]
[TD]194[/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[StephenCrump]

Can you help me ......

Duplicate hijack answered here: https://www.mrexcel.com/forum/excel...d-calculating-number-reach-specific-goal.html