Range figures that will count same value based on table range figures

[ambz123]

Hi I am trying to figure out how do i formula for a range to give the same amount.

E.g employee has a Wages amount of 1030.01 he should contribute $133 and employer should contribute $92.
Is there any formula that we can set to achieve?

Employee Wages Range	Employee Monthly Contribution	Employers Monthly Contribution
1020.01 - 1040.01	$ 133.00	$ 92.00

Employee-Provident-Fund-Calculator.xlsx
	A	B	C	D	E	F	G		I	J	K	L		N	O
1	Rate of increase of Basic Pay:		5%				figures to follow
2	Employer's Contribution:		13%				Employer's Contribution:			13%
3	Employee's Contribution:		9%				Employee's Contribution:			9%
4										Round down		Round down
5	Actual pay	Employee Monthly Contribution	rounddown	Employer Monthly Contribution	rounddown	Total Contribution	Wages Range		Employee Monthly Contribution	Employee Monthly Contribution	Employers Monthly Contribution	Employers Monthly Contribution		Rate of Interest
6
7	$ 1,030.00	$ 135.26	$ 135.00	$ 92.62	$ 92.00	$ 227.00	1020.01 - 1040.01		$ 132.92	$ 133.00	$ 92.43	$ 92.00		8.50%
8	$ 1,050.00	$ 137.89	$ 137.00	$ 94.42	$ 94.00	$ 231.00	1040.01 - 1060.01		$ 135.53	$ 136.00	$ 94.24	$ 94.00		8.50%
9	$ 1,070.00	$ 140.51	$ 140.00	$ 96.22	$ 96.00	$ 236.00	1060.01 - 1080.01		$ 138.14	$ 138.00	$ 96.06	$ 96.00		8.50%
10	$ 1,090.00	$ 143.14	$ 143.00	$ 98.02	$ 98.00	$ 241.00	1080.01 - 1100.01		$ 140.75	$ 141.00	$ 97.87	$ 97.00		8.50%
11	$ 1,110.00	$ 145.77	$ 145.00	$ 99.82	$ 99.00	$ 244.00	1100.01 - 1120.01		$ 143.36	$ 144.00	$ 99.69	$ 99.00		8.50%
12	$ 1,130.00	$ 148.40	$ 148.00	$ 101.62	$ 101.00	$ 249.00	1120.01 - 1140.01		$ 145.96	$ 146.00	$ 101.50	$ 101.00		8.50%
Sheet3

Cell Formulas
Range	Formula
B7:B12	B7	=A7*$C$2*1-N7
C7:C12	C7	=ROUNDDOWN(B7,0)
D7:D12	D7	=A7*$C$3*1-N7
F7:F12	F7	=C7+E7
K7:K12	K7	=H7*$J$3*1-N7

 

[Joe4]

If you create a lookup range that splits out the range into multiple columns, you can use VLOOKUP with the approximate match argument to do what you want.

Set up your VLOOKUP range like this:
Column 1: Lower range value
Column 2: Upper range value (optional)
Column 3: Employee Monthly Contribution
Column 4: Employer Monthly Contribution

And make sure that this range is sorted from low to high by column 1.
So, the first row in this lookup table would be:
Column 1: 1020.01
Column 2: 1040.00
Column 3: 133
Column 4: 92

If this lookup range were in the cells G7:J12, then the formula to return the Employee Contribution would be:

=VLOOKUP(A7,$G$7:$J$12,3,TRUE)

and the formula to return the Employer Contribution would be:

=VLOOKUP(A7,$G$7:$J$12,4,TRUE)

 

[ambz123]

Hi is there a way when i input the actual wages and it will auto lookup the employee monthly contribution and employer monthly contribution that falls in the range?
and the code do i use vba coding?

Employee-Provident-Fund-Calculator.xlsx
	A	B	C	D	E	F	G
1	Lower Range	Higher Range	Employee Monthly Contribution	Employer Monthly Contribution	To in Actual Wages here	Look up values for Employee	Look up values for Employer
2	1020.01	1040.01	136	94	1090	143	99
3	1040.01	1060.01	138	96
4	1060.01	1080.01	141	98
5	1080.01	1100.01	143	99
6	1100.01	1120.01	146	101
7	1120.01	1140.01	149	103
8	1140.01	1160.01	151	105
9	1160.01	1180.01	154	107
10	1180.01	1200.01	156	108
11	1200.01	1220.01	159	110
12	1220.01	1240.01	162	112
13	1240.01	1260.01	164	114
14	1260.01	1280.01	167	116
15	1280.01	1300.01	169	117
16	1300.01	1320.01	172	119
Sheet4

Cell Formulas
Range	Formula
F2	F2	=VLOOKUP(E2,$A:$D,3,TRUE)
G2	G2	=VLOOKUP(E2,$A:$D,4,TRUE)
A3:A16	A3	=B2
B3:B16	B3	=A3+20

 

[Joe4]

Sure. Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code into the VB Editor window that pops-up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long
    Dim rng As Range

'   If more than one cell entered at a time, skip
    If Target.CountLarge > 1 Then Exit Sub
    
'   See if entry is in column E after row 2
    If Target.Column = 5 And Target.Row >= 2 Then
        Application.EnableEvents = False
'       Find last row in column A with data
        lr = Cells(Rows.Count, "A").End(xlUp).Row
'       Build lookup range
        Set rng = Range("A2:D" & lr)
'       Lookup values and enter into columns F and G
        Target.Offset(0, 1) = Application.WorksheetFunction.VLookup(Target, rng, 3, True)
        Target.Offset(0, 2) = Application.WorksheetFunction.VLookup(Target, rng, 4, True)
        Application.EnableEvents = True
    End If

End Sub

This should automatically populae columns F and G as you enter data into column E.

 

[ambz123]

Hi thanks for the codes. However, right now I need to have more columns of data how do i change from the exisiting VBA code?

EPF Calculator.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			As of Jan 2020		As of Jan 2021 - Part A
2			11%	7%	12%	9%		As of Jan 2020			As of Jan 2021 - Part A
3	Lower Range	Higher Range	Employer Monthly Contribution	Employee Monthly Contribution	Employer Monthly Contribution	Employee Monthly Contribution	To key in Actual Wages here	Actual values for Employee	Actual values for Employer	Employee + Employer Contribution	Actual values for Employee	Actual values for Employer	Employee + Employer Contribution
4	MYR 1,020.00	MYR 1,040.00	MYR 136.00	MYR 73.00	$ 136.00	$ 94.00	MYR 1,044.55	$ 138.00	$ 75.00	$ 213.00
5	MYR 1,040.00	MYR 1,060.00	MYR 138.00	MYR 75.00	$ 138.00	$ 96.00	MYR 1,080.00
6	MYR 1,060.00	MYR 1,080.00	MYR 141.00	MYR 76.00	$ 141.00	$ 98.00
7	MYR 1,080.00	MYR 1,100.00	MYR 143.00	MYR 77.00	$ 143.00	$ 99.00
8	MYR 1,100.00	MYR 1,120.00	MYR 146.00	MYR 79.00	$ 146.00	$ 101.00
9	MYR 1,120.00	MYR 1,140.00	MYR 149.00	MYR 80.00	$ 149.00	$ 103.00
10	MYR 1,140.00	MYR 1,160.00	MYR 151.00	MYR 82.00	$ 151.00	$ 105.00
11	MYR 1,160.00	MYR 1,180.00	MYR 154.00	MYR 83.00	$ 154.00	$ 107.00
12	MYR 1,180.00	MYR 1,200.00	MYR 156.00	MYR 84.00	$ 156.00	$ 108.00
13	MYR 1,200.00	MYR 1,220.00	MYR 159.00	MYR 86.00	$ 159.00	$ 110.00
14	MYR 1,220.00	MYR 1,240.00	MYR 162.00	MYR 87.00	$ 162.00	$ 112.00
15	MYR 1,240.00	MYR 1,260.00	MYR 164.00	MYR 89.00	$ 164.00	$ 114.00
16	MYR 1,260.00	MYR 1,280.00	MYR 167.00	MYR 90.00	$ 167.00	$ 116.00
17	MYR 1,280.00	MYR 1,300.00	MYR 169.00	MYR 91.00	$ 169.00	$ 117.00
18	MYR 1,300.00	MYR 1,320.00	MYR 172.00	MYR 93.00	$ 172.00	$ 119.00
19	MYR 1,320.00	MYR 1,340.00	MYR 175.00	MYR 94.00	$ 175.00	$ 121.00
Test

Cell Formulas
Range	Formula
H4	H4	=VLOOKUP(G4,$A:$F,3,TRUE)
I4	I4	=VLOOKUP(G4,$A:$F,4,TRUE)
J4	J4	=H4+I4
A5:A19	A5	=B4
B5:B19	B5	=A5+20

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Test!$A$3:$F$352	H4:I4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H4:I147	Cell Value	=0	text	NO

 

[Joe4]

I am really not sure what you are trying to do now (and it is frustrating when you craft a solution - and then they change the requirements on you), so I will explain the sections of the code you may need to change so you can edit it yourself.

The first, is to identify which column you are entering data in to trigger the code to run. That is this line of code here:

    If Target.Column = 5 And Target.Row >= 2 Then

Originally it was column "E", which is the 5th column ("E" is the 5th letter of the alphabet).
So you would just change the number 5 to match whatever column data is being entered into.

Next, we need to define the range of our lookup table:

Set rng = Range("A2:D" & lr)

Assuming that you are still starting in column A, I think the only thing you may need to change is your ending column ("D").

Then, you need to update the line of code that does the lookup and places the value in the appropriate cell:

Target.Offset(0, 1) = Application.WorksheetFunction.VLookup(Target, rng, 3, True)

It is important to understand what "Target" is in these VBA procedures. "Target" is the range which triggers the code to run. So, in the first example, it was column E.
It is dynamic. So it was whatever cell in column E was just updated.

We wanted to place the first lookup in column F, so we started in the Target cell, and moved over one column to the right (that is what .Offset(0,1) does).
The format of Offset is Offset(rows, columns).

Judging from your original work you posted, I assume that you know how VLOOKUP works. So the only thing that should change in the formulas in the code is the third argument, which tell it which column from our lookup range to return (3rd in the line above).

In the original code I posted, we had to lines of code, to return the values to column F and G. To return more, just add similar ones, making the appropriate adjustment.

So based on the original code that I gave you, and the explanation above, you should be able to modify the code to do what you need.