Formula to extract automatically allowance value based on two salary and personal deduction scales

[vladimiratanasiu]

Hello!

I have a table with salary and personal deductions scales. The pay scale (A4:B44) is divided in ranges that cover a difference of maximum 2000 units, between the minimum reference salary (A2, B4) and the highest value (B44) considered when the personal deduction is calculated. Each interval (e.g. A4:B4, A5:B5 etc.) increases in values, with a difference of +50 units from the previous one. The personal deductions scale (C4:C44) covers the interval 20%-0% decreasingly, in steps of -0.5 percents. The minimum reference salary and pay scale values may vary, but the calculation scales 0-2000 units and 20%-0% are applied identically to every new table. I need a formula to find exactly the personal deduction percent (see e.g. G4:G12 results), according with the minimum reference and net salary values specified. The table A1:B44 is presented only for additional explanations. I wish that formula exclude it from direct references, but runs in a table structured as the Desired results (E1:G12) one.

Thank you!

Book1.xlsx
	A	B	C	D	E	F	G
1	Minimum reference salary				Desired results (e.g.)
2	4,000.00
3	Pay scale		Personal deduction		Minimum reference salary	Net salary	Personal deduction
4	0.00	4,000.00	20.00%		4,000.00	4,230.00	17.50%
5	4,001.00	4,050.00	19.50%			4,510.00	14.50%
6	4,051.00	4,100.00	19.00%			4,820.00	11.50%
7	4,101.00	4,150.00	18.50%		5,500.00	5,810.00	16.50%
8	4,151.00	4,200.00	18.00%			6,160.00	13.00%
9	4,201.00	4,250.00	17.50%			6,520.00	9.50%
10	4,251.00	4,300.00	17.00%		6,700.00	6,980.00	17.00%
11	4,301.00	4,350.00	16.50%			7,620.00	10.50%
12	4,351.00	4,400.00	16.00%			7,830.00	8.50%
13	4,401.00	4,450.00	15.50%
14	4,451.00	4,500.00	15.00%
15	4,501.00	4,550.00	14.50%
16	4,551.00	4,600.00	14.00%
17	4,601.00	4,650.00	13.50%
18	4,651.00	4,700.00	13.00%
19	4,701.00	4,750.00	12.50%
20	4,751.00	4,800.00	12.00%
21	4,801.00	4,850.00	11.50%
22	4,851.00	4,900.00	11.00%
23	4,901.00	4,950.00	10.50%
24	4,951.00	5,000.00	10.00%
25	5,001.00	5,050.00	9.50%
26	5,051.00	5,100.00	9.00%
27	5,101.00	5,150.00	8.50%
28	5,151.00	5,251.00	8.00%
29	5,252.00	5,250.00	7.50%
30	5,251.00	5,300.00	7.00%
31	5,301.00	5,350.00	6.50%
32	5,351.00	5,400.00	6.00%
33	5,401.00	5,450.00	5.50%
34	5,451.00	5,500.00	5.00%
35	5,501.00	5,550.00	4.50%
36	5,551.00	5,600.00	4.00%
37	5,601.00	5,650.00	3.50%
38	5,651.00	5,700.00	3.00%
39	5,701.00	5,750.00	2.50%
40	5,751.00	5,800.00	2.00%
41	5,801.00	5,850.00	1.50%
42	5,851.00	5,900.00	1.00%
43	5,901.00	5,950.00	0.50%
44	5,951.00	6,000.00	0.00%
Sheet2

Cell Formulas
Range	Formula
B4	B4	=A2
A5:A44	A5	=B4+1
B5	B5	=B4+50
B6	B6	=A2+100
B7	B7	=A2+150
B8	B8	=A2+200
B9	B9	=A2+250
B10	B10	=A2+300
B11	B11	=A2+350
B12	B12	=A2+400
B13	B13	=A2+450
B14	B14	=A2+500
B15	B15	=A2+550
B16	B16	=A2+600
B17	B17	=A2+650
B18	B18	=A2+700
B19	B19	=A2+750
B20	B20	=A2+800
B21	B21	=A2+850
B22	B22	=A2+900
B23	B23	=A2+950
B24	B24	=A2+1000
B25	B25	=A2+1050
B26	B26	=A2+1100
B27	B27	=A2+1150
B28	B28	=A6+1200
B29	B29	=A2+1250
B30	B30	=A2+1300
B31	B31	=A2+1350
B32	B32	=A2+1400
B33	B33	=A2+1450
B34	B34	=A2+1500
B35	B35	=A2+1550
B36	B36	=A2+1600
B37	B37	=A2+1650
B38	B38	=A2+1700
B39	B39	=A2+1750
B40	B40	=A2+1800
B41	B41	=A2+1850
B42	B42	=A2+1900
B43	B43	=A2+1950
B44	B44	=A2+2000

 

[hagia_sofia]

Hello, do you mean something along these lines:

=MAP(SCAN("",E4:E12,LAMBDA(a,b,IF(b<>"",b,a))),F4:F12,LAMBDA(x,y,
LET(
r,x,
s,SEQUENCE(41,,r,50),
d,SEQUENCE(41,,0.2,-0.005),
XLOOKUP(y,s,d,0,1))))

 

[vladimiratanasiu]

Hello, do you mean something along these lines:

=MAP(SCAN("",E4:E12,LAMBDA(a,b,IF(b<>"",b,a))),F4:F12,LAMBDA(x,y,
LET(
r,x,
s,SEQUENCE(41,,r,50),
d,SEQUENCE(41,,0.2,-0.005),
XLOOKUP(y,s,d,0,1))))

Thank you, hagia_sofia ! I adjusted your formula in my table, and it runs perfectly!

 

[hagia_sofia]

Many thanks for the feedback!