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]

In an earlier post, I presented one approach for this...although it doesn't use the LAMBDA function. Because it uses Excel's FACT function, there will be computational limits when N>=171...then other approaches are needed. In this post is a link to my file on Dropbox. In that Excel file on rows 24-26 you will see X term and Y term and Probability mentioned. Those refer to the X and Y terms on the web link in your post (at callcentrehelper)

Email and Chat FTE Calculation

Hi, Hope everyone is fine. Just need some help here. I need to calculate required FTE for our Email and Chat support group. Can anyone share calculation and computation here are the requirements: 1. Email Volume per week = 49k Handling Time = 3 minutes SLA #1= 90% volume ans in 12 hours SLA...

www.mrexcel.com

 

[Fluff]

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

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[greegan]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you. I wasn't sure where to look for the signature. It appears I had updated (which is current) my signature a while back.

 

[Fluff]

Can you please update your account details as requested (not you signature), as that is where most people look to see what version you are using. Thanks

 

[greegan]

In an earlier post, I presented one approach for this...although it doesn't use the LAMBDA function. Because it uses Excel's FACT function, there will be computational limits when N>=171...then other approaches are needed. In this post is a link to my file on Dropbox. In that Excel file on rows 24-26 you will see X term and Y term and Probability mentioned. Those refer to the X and Y terms on the web link in your post (at callcentrehelper)

Email and Chat FTE Calculation

Hi, Hope everyone is fine. Just need some help here. I need to calculate required FTE for our Email and Chat support group. Can anyone share calculation and computation here are the requirements: 1. Email Volume per week = 49k Handling Time = 3 minutes SLA #1= 90% volume ans in 12 hours SLA...

www.mrexcel.com

Thank you for your post. I am waiting for permission from my office to go beyond this forum (the dropbox) and will take a look when I can.
Really, the rest of the formula is done. I get the numbers they test on the CallCenterHelper website. Its only that I need to manually add more agents to N for the service level to increase.
This is where the recursive lambda would be helpful. As I understand it, it works like the function is a circular reference but returns an accurate result with no errors.
I'm looking for any help I can get with this.

 

[KRice]

If access is a problem, the relevant portion of the file is:

ErlangC_20210313.xlsx
	A	B	C	D	E	F	G
3	1. Inputs:
4	Number of calls	583		volume	49000	num/wk
5	In a period of minutes (min)	60		conv1	7	day/wk
6	Average Handling Time (AHT) (s)	180		conv2	12	hr/day
7	Required Service Level	90%		conv3	60	min/hr
8	Target Answer Time (s)	43200			9.722222222	num/min
9	Maximum Occupancy	80%		time basis	60	min
10	Shrinkage	21%		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)	583		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	583
17	Avg time per call (min)	3
18	Call-minutes per hour	1749
19	Traffic intensity (A) , call-hours per hour (Erlang unitless)	29.15
20
21	4. Estimate the raw number of agents required (N)
22	One agent max capability is 1 call-hour per hour actual time
23	Initial guess for N agents required	30.15	30	32	34	36	38
24	X term	1.63863E+13	1.15664E+13	3.15186E+12	1.49033E+12	7.53468E+11	3.72036E+11
25	Y term	2457766582008.63	2457766582008.63	3093639949663.06	3622315635137.58	4011965941098.52	4268285119753.46
26	Probability a call has to wait, Pw	86.96%	82.47%	50.47%	29.15%	15.81%	8.02%
27
28	5. Calculate the Service Level (list item #13 in ref)
29	Service Level	100.00%	100.00%	100.00%	100.00%	100.00%	100.00%
30	Target Service Level	90%	90%	90%	90%	90%	90%
31	Target Service Level Met?	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
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
B23	B23	=B19+1
B24:G24	B24	=B$23/FACT(B$23)/(B$23-$B$19) * $B$19^B$23
B25:G25	B25	=IF(B$23<2, 1, 1 + SUMPRODUCT($B$19^ROW($A$1:INDEX($A:$A, B$23-1)) / FACT(ROW($A$1:INDEX($A:$A, B$23-1)))))
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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B31:G31	Cell Value	=TRUE	text	NO

...where the probability formula is simplified into X and Y terms referenced in the worksheet (lines 24 and 25, the factorials and summations are handled by the single-line formulas):

 

[greegan]

View attachment 62407

Thank you for the posted file information. But I think the point is being missed.
I have everything but N. N is the variable when Service Level doesn't reach 80%. When it doesn't meet that minimum of 80% N needs to be calculated again. I want N to calculate automatically when SL<.8 so it adds a 1, recalculates, and then determines if it meets the criteria and adds another 1 if it doesn't meet that 80%.
My formula works fine, and I can enter increased amounts of the value of N until I reach the optimum Service Level, but I forecast on 15-minute intervals for three weeks at a time, so that would be many manual adjustments of N I should be able to avoid with a recursive LAMBDA function because what I've described above will be a circular reference otherwise.

 

[greegan]

The value of Y, for example is a LAMBDA function I was able to create.

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

Add this to the name manager and name it Series_Y
Then for the value of Y the formula is simply

=Series_Y(Erlangs,N-1)

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

And the above is my Service Level formula. But again if it doesn't reach 80%, we need to add 1 to N, and like I said its a circular reference that, I believe, a recursive LAMBDA function would resolve.
Maybe the whole thing needs to be recursive to function right.

-- g

 

[KRice]

I see...yes, the sheet I posted requires some user intervention for the N variable. The user inputs guesses for N to bound the problem and then fine tunes the guesses to zero in on the minimum N to satisfy conditions.