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 the last sheet that I posted, I adjusted the inputs to look like this:

1. Inputs:
Number of calls	100
In a period of minutes (min)	30
Average Handling Time (AHT) (s)	370
Required Service Level	80%
Target Answer Time (s)	20
Maximum Occupancy	85%
Shrinkage	30%

...and the middle part of the worksheet looks like this (I tweaked the starting N formula to roundup traffic intensity A to the next whole number and then added a user-selectable incrementing factor to see what the Probabilities and Service Levels are as the number of agents are increased...those are in highlighted yellow). I don't see any issues. The Probabilities that a call has to wait decreases as agents are added, while the Service Level metric increases, finally exceeding 80 % SL when N=26. The FindN LAMBDA function (red highlighting) also returns 26. Are you seeing something different?

ErlangC_20210313_v2.xlsx
	A	B	C	D	E	F	G
19	Traffic intensity (A) , call-hours per hour (Erlang unitless)	20.55555556
20
21	4. Estimate the raw number of agents required (N)	26
22	One agent max capability is 1 call-hour per hour actual time		1	<--increment by
23	Initial guess for N agents required	21	22	23	24	25	26
24	X term [in Probability, Pw = X/(X+Y)]	3448033681	1038479823	573357202.3	363653391.5	241383356.5	162016946.8
25	Y term	431140842.34	504115100.14	572298118.80	633234633.05	685425629.06	728338225.78
26	Probability a call has to wait, Pw	88.89%	67.32%	50.05%	36.48%	26.04%	18.20%
27
28	5. Calculate the Service Level (list item #13 in ref)
29	Service Level	13.22%	37.74%	56.15%	69.72%	79.52%	86.44%
30	Target Service Level	80%	80%	80%	80%	80%	80%
31	Target Service Level Met?	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE
ErlangC

Cell Formulas
Range	Formula
B19	B19	=B18/60
B21	B21	=FindN(B6,B8,B19,B23,B7)
B23	B23	=ROUNDUP(B19,0)
C23	C23	=ROUNDUP(B23,0)+$C$22
D23:G23	D23	=C23+$C$22
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

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

 

[greegan]

I noticed the formula for Y is different from what I'm using,
You

=SERIESSUM($B$19,0,1,1/(FACT(SEQUENCE(B$23,,0))))

And if I were to show the LAMBDA function basic formula it would be

=SERIESSUM($B$19,0,1,1/(FACT(SEQUENCE(B$23+1,,0))))

By removing the +1, that may work for me

 

[greegan]

UPDATE: I am still working on this but work pulled me to another project. I will return to this once that has completed.

 

[greegan]

UPDATE: I am still working on this but work pulled me to another project. I will return to this once that has completed.

I am back on this project and will update with results and if so, resolved, when applicable.

 

[KRice]

I revisited this some time ago and took a different approach altogether. I'll dig that up and provide some details.

 

[greegan]

I revisited this some time ago and took a different approach altogether. I'll dig that up and provide some details.

KK, I'll be adding stuff here when I can. Look forward to seeing you approach to this.