How to indentify needed Call Duration based on a Service Level Target

Im looking for a way to indentify needed Call Duration when I have the following information.
Calls per Interval = 437
Interval Seconds = 1800
Number of people = 98
Target Answer Time = 40secs
Service Level = 80%
How do I indentify the highest possible call duration for that interval and answer 80% of those calls (437) within 40secs?
I'm looking of an excel formula if possible.....
redrpAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

J79123Commented:
I don't think there is a "highest possible", or maximize sort of thing here, because there is no variable you can change. You have a target of 80%, so it would just be a straight formula.
 So if I'm understanding your question this is just a math question with a straight formula answer. Is this a homework problem? :)

This is what I came up with = 468 seconds/call

I broke it down on the attached spreadsheet as little formulas and one big one as well.

Number of Calls       437       calls
Number of Phones or People       98       phones
Time to answer (Lost Time)       40       seconds
Interval in Seconds       1,800       seconds
Target % of Calls to answer      80%      
            
Total Seconds available=Phones x Interval
=176,400.00 seconds
            
Time to Answer (Lost time) = Number of Calls x Time to answer x Target % of calls to answer       =13,984.00 seconds
            
Available time for call duration = Total Seconds avail. - Time to Answer targeted calls      
=162,416      seconds
            
Calls Answered = Target % x # of Calls
=349.60 calls
            
Duration of each answered Calls
= 464.58       seconds
            
Duration as one formula:
((Phone x Interval) - (Number of Calls x Time to Answer x Target %))/(# of Calls x Target %)       


phones.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:
redrp,

Native Excel functions cannot handle Erlang C queuing calculations.  You would need a UDF.

That said, there are numerous free-to-cheap Erlang C calculators available on the web.  I would simply take one and start fiddling with it until your answers start to converge.

J79123,

This question is about queuing using the Erlang C algorithm, which assumes that calls arrive randomly in a Poisson distribution, and for which a certain service level is the goal.  It is typically used to determine the number of agents required to handle the call volume given the expected volume, the avg handle time, and the service level, but of course it can be turned around to output one of the input parameters instead if we make the number of agents a given.

:)

Patrick
0
redrpAuthor Commented:
Hey Guys,

I am sorry I haven't responded. I am looking at this and will close out the question today, for the record, this is not a homework question :)  

Thanks,

John
0
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.