Creating a Formula in a Single Cell with LAMBDA

[SelamT]

Hello,

I have a formula with several steps to find the gross of the net wage. for this to happen, I need to use the "Circular Reference". In my research, I found out that it is possible to write recursive formulas with the LAMBDA function and that the "circular reference" option is not needed (I may have misunderstood)

For example, can I find the gross of net pay by typing multiple formulas I have typed in separate cells below, in just one Excel cell (without using a circular reference)?

recursive_maas.xlsx
	A	B	C	D	E	F	G
1	Month	Net	Day	Gross	Base	Social	Unemployment
2	1	17002,12	30	20.002,50	20002,5	2800,35	200,03
Sheet1

Cell Formulas
Range	Formula
D2	D2	=B2+F2+G2
E2	E2	=IF(D2>150018.9,150018.9,D2)
F2	F2	=ROUND(E2*14%,2)
G2	G2	=ROUND(E2*1%,2)

Thanks,

 

[Dave Patton]

You may not require Lambda.
Please check the following.

Lambda 2024.xlsm
	A	B	C	D	E	F	G
1		Net		Gross		Social	Unemployment
2		17,002.13		20,002.51		2,800.35	200.03
3		50,000.00		58,823.55		8,235.30	588.24
4		127,516.06		150,018.94		21,002.65	1,500.19
5		300,000.00		322,502.84		21,002.65	1,500.19
6		17,002.13		20,002.51		2,800.35	200.03
7		50,000.00		58,823.55		8,235.30	588.24
8		127,516.06		150,018.94		21,002.65	1,500.19
9		300,000.00		322,502.84		21,002.65	1,500.19
10
1e

Cell Formulas
Range	Formula
F2:F9	F2	=MIN(D2*0.14,150018.9*0.14)
G2:G9	G2	=MIN(D2*0.01,150018.9*0.01)
D2:D5	D2	=IF(B2<UAmt,B2*Num,B2+21002.65+1500.19)
D6:D9	D6	=GrossCalc(B6)

Lambda Functions
Name	Formula
GrossCalc	=LAMBDA(refNet,LET(n, refNet,IF(n<127516.07,n*Num,n+21002.65+1500.19)))

 

[Dave Patton]

Lambda 2024.xlsm
	A	B	C	D	E	F	G
1		Net		Gross		Social	Unemployment
2		17,002.12		20,002.49		2,800.35	200.02
3		50,003.00		58,827.06		8,235.79	588.27
4		127,516.10		150,018.94		21,002.65	1,500.19
5		300,000.00		322,502.84		21,002.65	1,500.19
6		17,002.12		20,002.49		2,800.35	200.02
7		50,000.00		58,823.53		8,235.29	588.24
8		127,516.10		150,018.94		21,002.65	1,500.19
9		300,000.00		322,502.84		21,002.65	1,500.19
1e

Cell Formulas
Range	Formula
F2:F9	F2	=ROUND(MIN(D2*0.14,21002.65),2)
G2:G9	G2	=ROUND(MIN(D2*0.01,1500.19),2)
D2:D5	D2	=IF(B2<127516.09,B2*1.17647059285,B2+21002.65+1500.19)
D6:D9	D6	=GrossCalc(B6)

Lambda Functions
Name	Formula
GrossCalc	=LAMBDA(refNet,LET(n, refNet,Num,1.17647059285,IF(n<127516.09,n*Num,n+21002.65+1500.19)))

 

[SelamT]

Hello @Dave Patton,

First of all, thank you for your answers.
However, I could not understand the reasons for the numbers you used in the formulas.
The number that can only be used in the formula should be 150018.9. If the gross to be found is greater than 150018.9, this figure should be taken as the basis for the rates to be applied to social security and unemployment premiums.

I just want a function to write =GrossCalc(B2;C2) and find the gross wage.

Thanks,

 

[Dave Patton]

Did you try either the formulas or the Lambda?
N.B. You can paste the example into a clean sheet.
Click on the icon below the f(x) in the heading, move to your sheet and paste into cell A1.

To use the Lambda, use Name Manager copy the information
=new put GrossPay or a name that you prefer GrossCalc should be OK
value = the Lambda information

Lambda 2024.xlsm
	A	B	C	D	E	F	G
1		Net		Gross		Social	Unemployment
2		100,000.00		117,647.06		16,470.59	1,176.47
3		400,000.00		422,502.84		21,002.65	1,500.19
1e

Cell Formulas
Range	Formula
F2:F3	F2	=ROUND(MIN(D2*0.14,21002.65),2)
G2:G3	G2	=ROUND(MIN(D2*0.01,1500.19),2)
D2:D3	D2	=GrossCalc(B2)

Lambda Functions
Name	Formula
GrossCalc	=LAMBDA(refNet,LET(n, refNet,Num,1.17647059285,IF(n<127516.091,n*Num,n+21002.65+1500.19)))

 

[Dave Patton]

GrossCalc prompts for the reference cell (refNet) and then calculates the Gross Pay.

Does it work for you? The formula that does not use Lambda also works.

Lambda 2024.xlsm
	A	B	C	D
1		Net		Gross
2		100,000.00		117,647.06
3		45,000.00		52,941.18
4		1,000,000.00		1,022,502.84
5		or
6		500,000.00		522,502.84
7		45,000.00		52,941.18
8		100,000.00		117,647.06
1e

Cell Formulas
Range	Formula
D2:D4	D2	=GrossCalc(B2)
D6:D8	D6	=IF(B6<127516.09,B6*1.17647059285,B6+21002.65+1500.19)

Lambda Functions
Name	Formula
GrossCalc	=LAMBDA(refNet,LET(n, refNet,Num,1.17647059285,IF(n<127516.091,n*Num,n+21002.65+1500.19)))

 

[SelamT]

Yes, I tried both of your formulas. Thank you.

I just don't understand the numeric values you defined in LAMBDA, because there shouldn't be a numeric value like that. Only ratios like 0.14 and 0.1 can be used.

If a net figure is greater than 150018.90, the ratios 0.14 and 0.1 should be applied to 150018.90.

 

[Dave Patton]

The suggestions that I provided work.
The Social and Unemployment are calculated on the Gross.
Try reviewing the example with Formulas Evaluate.

 

[SelamT]

I guess you did not understand what I meant I am saying that you should not use fixed numbers in the formula.

I am writing you a UDF, this code does what I want. However, I want to do it with a formula using LAMBDA, not with vba. My goal is to learn how to use LAMBDA.

Function CalculateGrossSalary(NetSalary As Double) As Double
    Const SocialSecurityRate As Double = 0.14
    Const UnemploymentRate As Double = 0.01
    Const MaximumSalary As Double = 150018.9

    Dim EstimatedGross As Double
    Dim CalculatedNet As Double
    Dim Difference As Double

    EstimatedGross = NetSalary

    Do
        Dim SSK As Double, Unemployment As Double
        SSK = WorksheetFunction.Round(WorksheetFunction.Min(EstimatedGross, MaximumSalary) * SocialSecurityRate, 2)
        Unemployment = WorksheetFunction.Round(WorksheetFunction.Min(EstimatedGross, MaximumSalary) * UnemploymentRate, 2)
        CalculatedNet = EstimatedGross - SSK - Unemployment

        Difference = NetSalary - CalculatedNet

        If Abs(Difference) < 0.0001 Then Exit Do

        EstimatedGross = EstimatedGross + Difference
    Loop

    CalculateGrossSalary = EstimatedGross
End Function

 

[Dave Patton]

You can review samples of Lambdas with Recursion in the Lambda section of this forum.
There are also examples on the internet.