Need recursive LAMBDA help please

[greegan]

I am working on an Erlang C formula and was having trouble until I found out about LAMBDA functions. They are awesome!
My formula is currently calculating the Service Level of our contact centre with a specific number of agents. If the result does not meet the target (in this case 80%) we need to more agents until it reaches at least the target or better.
I only need help with this portion of the formula. I believe a recursive LAMBDA will work with this but I'm not comprehending the syntax. Major brain fart here. Here is what I'm thinking:

target = .8
sl = current service level = ?
n = number of agents already considered
Naming LAMBDA function SLT

=LAMBDA(sl,target,n,if(sl<target,SLT+1,0))

The issue I'm running into is that the Service Level already has a formula and will be a circular reference because it is dependent on the total of n.

I'm likely going to making the whole formula into a LAMBDA function if I can get help on the above step.

If you need more information, I can include all of the factors and functions used for the final numbers.

My formulas are based on the ErlangC formula from wikipedia

And the explanation HERE


Thank you for your help.


-- g







If its not in my signature I'm using Office 365 for Windows 10.

 

[KRice]

I don't follow your LAMBDA formula for the Y series.

SERIESSUM(a,0,1,1/(FACT(SEQUENCE(i+1,,0))))

...gives a summation of a power series, but the factorial in the denominator of the Y term (i.e., i!) is not raised to an increasing power (so it isn't a power series). The numerator (i.e., A^i) also seems to be missing. Am I missing something? It appears that you have assigned names for Erlangs, Target ASA, and Target AHT. I'm assuming the latter two are Average Speed of Answer and Average Handling Time, but how is Erlangs defined?

 

[greegan]

If I do get this resolved I will post my final formula.

 

[KRice]

I think you want something like this (substitute your i expression for the "4")...I'm showing the 1st four terms of the sum, breaking out each component to follow what the formula is doing:

Book2
	A	B	C	D	E	F	G	H
4					Numerator	Denominator		4515.333
5					A terms	Fact(i) terms	Manual Division	Formula
6	Traffic intensity, A	29		4515.33	1	1	1	1
7					29	1	29	29
8					841	2	420.5	420.5
9					24389	6	4064.833	4064.833
Sheet1

Cell Formulas
Range	Formula
H4	H4	=SUM(H6#)
D6	D6	=SUM(B6^SEQUENCE(4,,0)/(FACT(SEQUENCE(4,,0))))
E6:E9	E6	=B6^SEQUENCE(4,,0)
F6:F9	F6	=FACT(SEQUENCE(4,,0))
G6:G9	G6	=E6#/F6#
H6:H9	H6	=B6^SEQUENCE(4,,0)/(FACT(SEQUENCE(4,,0)))
Dynamic array formulas.

 

[greegan]

For an example of the stats I will use the numbers from the Call Centre Helper link I provided in my earlier post and place them here:

• Number of calls – 100
• In a period of minutes – 30
• Average Handling Time (seconds) – 180 (3 minutes)
• Required Service Level 80%
• Target Answer Time (Seconds) 20
• Maximum Occupancy 85%
• Shrinkage 30%

I will include an abbreviated list of steps from that site:

Work Out the Number of Calls Per Hour​

So, we have 100 calls per 30 minutes, which works out as 200 calls per hour.

3. Work Out the Traffic Intensity (A)​

200 calls with an Average Handling Time of 3 minutes, we would have a total of 200 x 3 = 600 call minutes.
600 call minutes / 60 = 10 Call Hours.
So the traffic intensity = 10 Call Hours = 10 Erlangs.

4. Estimate the Raw Number of Agents Required (N)​

Estimate 1: N = A + 1 = 10 + 1 = 11 Agents

5. Calculate the Erlang Formula for Probability a Call Waits​

6. Work Out N! (N Factorial)​

So N! = 11! = 11 Factorial = 11 x 10 x 9 x 8 x 7 x 6 x 5 x 4 x 3 x 2 x 1 = 39916800
Excel formula
N! = 11! = 11 Factorial

= FACT (11)

= 39916800

And I am aware the FACT function won't allow above 170 however we are a small company and it may take years to reach that number of agents on one project.

8. Work Out the Powers – AN​

(not sure how to superscript fonts on the forum. For the sake of argument, the above is A to the power of N)

AN = 1011

= POWER (10,11)

= 1 x 1011

9. Let’s Simplify the Erlang C Formula​

We can use a substitution, to substitute X and Y for the complex parts of the equation: Pw = X / ( Y + X)

10. Let’s Work Out the Top Row of the Erlang Formula (X)​

X = 27557.32

11. Work Out the Sum of a Series (Y)​

=LAMBDA(a,i,ROUND(SERIESSUM(a,0,1,1/(FACT(SEQUENCE(i+1,,0)))),1))

A = 10 = Erlangs
i=N-1

=ROUND(SERIESSUM(A,0,1,1/(FACT(SEQUENCE(i+1,,0)))),1))

This means that the sum of the series Y = 12842.3

12. Put X and Y into the Erlang C Formula (The probability a call has to wait)​

So Pw = X / ( Y + X)

Pw = 27557 / (12842 + 27557) = 0.6821

13. Calculate the Service Level​

Service Level follows the formula:

=1-([@Pw]*POWER([@e],(([@N]-[@Erlangs])*([@[Target ASA]]/[@[Target AHT]]))))

=1-(27557 / (12842 + 27557)*POWER(2.71828,((11-10)*(20/180))))

• Target AHT = Average Handling Time (seconds) – 180 (3 minutes)
• Target ASA = Target Answer Time (Seconds) 20
• e =2.71828
• Pw = 27557 / (12842 + 27557)
• A = 10 = Erlangs
• N = A + 1 = 10

Where e is the mathematical constant (Euler’s number) and will always equal 2.71828.

Excel has an equation for this =EXP()

Let’s work out -(N – A) * (TargetTime / AHT)

Let’s work out -(11 – 10) * (20 /180) = -0.111

Then work out Service Level

SL = 1- (0.6821 * EXP(-0.11111)) = 0.390

Service Level as a Percentage = 39.0%

This is below the target of 80%, so we need to increase the number of agents.



These are all numbers I'm getting with my formulas, matching precisely with any adjustment to N


So the recursive LAMBDA formula would need to take the Service Level into account and adjust automatically until SL is met.

I hope that makes more sense.

 

[KRice]

Thanks, have a look at my last post, which pertains to your section 11 calculation.

 

[greegan]

Thanks, have a look at my last post, which pertains to your section 11 calculation.

The Y formula isn't the issue I'm running into because it calculates correctly.

My formulas give the same results the site gives and when I increase the value of N the numbers for Pw and SL return the same. I just need the N the auto correct when the SL is not 80%.

 

[KRice]

I have an approach that returns the raw number of agents, N, that satisfy the target service level. In the Name Manager, create two names for LAMBDA functions (ServiceLevel and FindN) with the following "Refers to" expressions:

For ServiceLevel:

=LAMBDA(aht,tat,a,ndx,LET(X,ndx*a^ndx/FACT(ndx)/(ndx-a),Y,SERIESSUM(a,0,1,1/(FACT(SEQUENCE(ndx,,0)))),P,X/(X+Y),1-P*EXP(-(ndx-a)*(tat/aht))))

For FindN:

=LAMBDA(aht,tat,a,ndx,tsl,IF(ServiceLevel(aht,tat,a,ndx)<tsl,FindN(aht,tat,a,ndx+1,tsl),ndx))

The ServiceLevel LAMBDA takes arguments for Average Handling Time, Target Answer Time, Traffic Intensity A, and Number of Agents N, in that order (I've called these arguments (aht, tat, a, ndx)). If you'd like, you can confirm the performance by pasting this same expression in a worksheet and then follow the expression with another set of parentheses in which you point to the cell references where these arguments are found (e.g., (B6,B8,B19,B23)). This LAMBDA is fairly straightforward to understand. After receiving the arguments, the LET function is used to establish values for the X and Y terms, calculate probability P from X and Y, and then finally perform the calculation for Service Level, which depends on the preceding arguments/expression values. This LAMBDA, therefore, returns Service Level for a given set of inputs. This LAMBDA sits behind the scenes and waits to be called by the FindN LAMBDA.

The FindN LAMBDA is where the iterations occur. Since it calls the ServiceLevel LAMBDA, we need to ensure that it has arguments (aht, tat, a, ndx) so that those values are available for passing along to ServiceLevel. But we also need the FindN LAMBDA to perform the logic check to determine if the target service level (tsl) has been reached as the number of agents is increased; therefore, we also supply tsl to this LAMBDA...these are the first five terms, supplied in that order. Next, an IF statement is created to check whether the Service Level threshold has been met. This logic statement is written such that if the logic results in TRUE that means to iterate (we are not done yet...the threshold value for number of agents has not yet been found because the Service Level is still too low), and a FALSE means to exit the loop. A logic result of TRUE executes FindN(aht,tat,a,ndx+1,tsl). Note that all five arguments are passed along, except the number of agents (I think of as an index, hence "ndx") is increased by 1. These five arguments are in the same order as the initial list encountered when entering the LAMBDA expression. So the iterations begin with the initial IF(ServiceLevel<tsl test, where ServiceLevel uses the initial seed value for ndx, and upon getting a TRUE result (service level not yet met), the FindN LAMBDA is called, where ndx is increased by 1 (ndx+1 in the argument list), and this ndx+1 is fed back into the ServiceLevel logic test until the expression evaluates to FALSE, meaning the current value of "ndx" supplied to ServiceLevel results in tsl being met. The loop terminates and "ndx" is returned as the result.

=LAMBDA(aht,tat,a,ndx,tsl,
     IF(ServiceLevel(aht,tat,a,ndx)<tsl,
          FindN(
               aht,tat,a,ndx+1,tsl
                     ),
           ndx
        )
                  )(B6,B8,B19,B23,B7)

The previous worksheet I posted performs a similar function by relying on multiple columns B23:G23 where increasing numbers of agents are evaluated, and then logic under those columns checks to determine if the Service Level is met, and later, whether the Maximum Occupancy constraint is met, along with a final determination on whether all conditions are met while accounting for the specified shrinkage factor. I discovered that I misinterpreted the SERIESSUM function, as you included the factorial component in the coefficient part of the SERIESSUM argument list and not in the part subjected to increasing powers...so all is well there (as you pointed out), and your expression is equivalent to the original one in my sheet and to the other form I suggested in post #13---equivalent, but also more elegant. I like your implementation of the Y term, so I've revised my sheet to use it. I've also added a red highlighted cell (B21) to show how FindN is called, and how it performs the work handled by formulas in C23:G23 and A24:G31. A trimmed down version of this worksheet could eliminate most of that range without any loss of functionality.

ErlangC_20210313_v2.xlsx
	A	B	C	D	E	F	G
1	Tool for Estimating Staffing for Call Center using Erlang C Method
2
3	1. Inputs:
4	Number of calls	100		volume	49000	num/wk
5	In a period of minutes (min)	30		conv1	7	day/wk
6	Average Handling Time (AHT) (s)	180		conv2	12	hr/day
7	Required Service Level	80%		conv3	60	min/hr
8	Target Answer Time (s)	20			9.722222222	num/min
9	Maximum Occupancy	85%		time basis	60	min
10	Shrinkage	30%		number of calls	583.3333333	num/time basis
11
12	2. Determine number of calls per hour			response time	12	hr
13	n_avg (# calls/h)	200		conv1	60	min/hr
14				conv2	60	s/min
15	3. Determine the Traffic Intensity (A)(hour basis)			target ans time	43200	sec
16	# of calls in hour basis	200
17	Avg time per call (min)	3
18	Call-minutes per hour	600
19	Traffic intensity (A) , call-hours per hour (Erlang unitless)	10
20
21	4. Estimate the raw number of agents required (N)	14
22	One agent max capability is 1 call-hour per hour actual time
23	Initial guess for N agents required	11	12	13	14	15	16
24	X term [in Probability, Pw = X/(X+Y)]	27557.31922	12526.05419	6958.918996	4014.760959	2294.14912	1274.527289
25	Y term	12842.31	15347.52	17435.19	19041.10	20188.17	20952.89
26	Probability a call has to wait, Pw	68.21%	44.94%	28.53%	17.41%	10.20%	5.73%
27
28	5. Calculate the Service Level (list item #13 in ref)
29	Service Level	38.96%	64.02%	79.56%	88.84%	94.15%	97.06%
30	Target Service Level	80%	80%	80%	80%	80%	80%
31	Target Service Level Met?	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE
32
33	6. Average Speed of Answer (ASA) (list item #15 in ref)
34	Avg Speed of Answer (ASA) (seconds)	122.7812768	40.44494019	17.11622718	7.835937012	3.673525212	1.720209927
35
36	7. Percentage of Calls Answered Immediately (list item #16 in ref)
37	Immediate Answer	31.79%	55.06%	71.47%	82.59%	89.80%	94.27%
38
39	8. Check Maximum Occupancy (list item #17 in ref)
40	Occupancy	90.91%	83.33%	76.92%	71.43%	66.67%	62.50%
41	Max Occupancy	85%	85%	85%	85%	85%	85%
42	Max Occupancy constraint met?	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE
43
44	9. Factor in Shrinkage (list item #18 in ref)
45	Number agents needed considering shrinkage of 30%	16	18	19	20	22	23
46
47	10. Summary
48	Number agents needed (minimum of #9 where #5 and #8 are satisfied)	20
49	Service Level	88.8%
50	Probability a call has to wait	17.4%
51	Average Speed of Answer (seconds)	7.8
52	% of calls Answered Immediately	82.6%
ErlangC

Cell Formulas
Range	Formula
E8	E8	=E4/E5/E6/E7
E10,B18	E10	=E8*E9
B13	B13	=B4/(B5/60)
E15	E15	=E12*E13*E14
B16	B16	=B13
B17	B17	=B6/60
B19	B19	=B18/60
B21	B21	=FindN(B6,B8,B19,B23,B7)
B23	B23	=B19+1
B24:G24	B24	=B$23/FACT(B$23)/(B$23-$B$19) * $B$19^B$23
B25:G25	B25	=SERIESSUM($B$19,0,1,1/(FACT(SEQUENCE(B$23,,0))))
B26:G26	B26	=B24/(B24+B25)
B29:G29	B29	=1-B26*EXP(-(B23-$B$19)*($B$8/$B$6))
B30:G30	B30	=$B$7
B31:G31	B31	=B29>=B30
B34:G34	B34	=B26*$B$6/(B23-$B$19)
B37:G37	B37	=1-B26
B40:G40	B40	=$B$19/B23
B41:G41	B41	=$B$9
B42:G42	B42	=B40<B41
A45	A45	="Number agents needed considering shrinkage of "& TEXT($B$10,"0%")
B45:G45	B45	=ROUNDUP(B23/(1-$B$10),0)
B48	B48	=AGGREGATE(15,6,(B45:G45)/((B31:G31)*(B42:G42)),1)
B49	B49	=INDEX($B$29:$G$29,MATCH(B$48,$B$45:$G$45,0))
B50	B50	=INDEX($B$26:$G$26,MATCH(B$48,$B$45:$G$45,0))
B51	B51	=INDEX($B$34:$G$34,MATCH(B$48,$B$45:$G$45,0))
B52	B52	=INDEX($B$37:$G$37,MATCH(B$48,$B$45:$G$45,0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B45:G45	Expression	=AND(B31,B42)	text	NO
B42:G42	Cell Value	=TRUE	text	NO
B31:G31	Cell Value	=TRUE	text	NO

 

[greegan]

Thank you. This is awesome KRice! I am running into numbers below 80% when the AHT is above 329 seconds which most of our AHT is at our contact center.
I was using the 180 so the formula would match the site's numbers first. Our Target AHT is 370. Do you see how this could be corrected? Maybe I missed something on my formulas?
Thank you again!

-- g

 

[greegan]

Thank you. This is awesome KRice! I am running into numbers below 80% when the AHT is above 329 seconds which most of our AHT is at our contact center.
I was using the 180 so the formula would match the site's numbers first. Our Target AHT is 370. Do you see how this could be corrected? Maybe I missed something on my formulas?
Thank you again!

-- g

I should also mention the above numbers do not include Shrinkage or Occupancy at this time.

 

[KRice]

I’ll have a look when I get back to my computer and post back.