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

ambz123

Board Regular
Joined
Aug 27, 2020
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGIJKLNO
1Rate of increase of Basic Pay:5%figures to follow
2Employer's Contribution:13%Employer's Contribution:13%
3Employee's Contribution:9%Employee's Contribution:9%
4Round downRound down
5Actual payEmployee Monthly Contributionrounddown Employer Monthly ContributionrounddownTotal ContributionWages Range Employee Monthly ContributionEmployee Monthly ContributionEmployers Monthly ContributionEmployers Monthly ContributionRate 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.008.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.008.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.008.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.008.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.008.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.008.50%
Sheet3
Cell Formulas
RangeFormula
B7:B12B7=A7*$C$2*1-N7
C7:C12C7=ROUNDDOWN(B7,0)
D7:D12D7=A7*$C$3*1-N7
F7:F12F7=C7+E7
K7:K12K7=H7*$J$3*1-N7
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
=VLOOKUP(A7,$G$7:$J$12,3,TRUE)
and the formula to return the Employer Contribution would be:
VBA Code:
=VLOOKUP(A7,$G$7:$J$12,4,TRUE)
 

ambz123

Board Regular
Joined
Aug 27, 2020
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFG
1Lower Range Higher Range Employee Monthly ContributionEmployer Monthly ContributionTo in Actual Wages here Look up values for Employee Look up values for Employer
21020.011040.0113694109014399
31040.011060.0113896
41060.011080.0114198
51080.011100.0114399
61100.011120.01146101
71120.011140.01149103
81140.011160.01151105
91160.011180.01154107
101180.011200.01156108
111200.011220.01159110
121220.011240.01162112
131240.011260.01164114
141260.011280.01167116
151280.011300.01169117
161300.011320.01172119
Sheet4
Cell Formulas
RangeFormula
F2F2=VLOOKUP(E2,$A:$D,3,TRUE)
G2G2=VLOOKUP(E2,$A:$D,4,TRUE)
A3:A16A3=B2
B3:B16B3=A3+20
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

Board Regular
Joined
Aug 27, 2020
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLM
1As of Jan 2020 As of Jan 2021 - Part A
211%7%12%9% As of Jan 2020 As of Jan 2021 - Part A
3Lower 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
4MYR 1,020.00MYR 1,040.00MYR 136.00MYR 73.00$ 136.00$ 94.00MYR 1,044.55$ 138.00$ 75.00$ 213.00
5MYR 1,040.00MYR 1,060.00MYR 138.00MYR 75.00$ 138.00$ 96.00MYR 1,080.00
6MYR 1,060.00MYR 1,080.00MYR 141.00MYR 76.00$ 141.00$ 98.00
7MYR 1,080.00MYR 1,100.00MYR 143.00MYR 77.00$ 143.00$ 99.00
8MYR 1,100.00MYR 1,120.00MYR 146.00MYR 79.00$ 146.00$ 101.00
9MYR 1,120.00MYR 1,140.00MYR 149.00MYR 80.00$ 149.00$ 103.00
10MYR 1,140.00MYR 1,160.00MYR 151.00MYR 82.00$ 151.00$ 105.00
11MYR 1,160.00MYR 1,180.00MYR 154.00MYR 83.00$ 154.00$ 107.00
12MYR 1,180.00MYR 1,200.00MYR 156.00MYR 84.00$ 156.00$ 108.00
13MYR 1,200.00MYR 1,220.00MYR 159.00MYR 86.00$ 159.00$ 110.00
14MYR 1,220.00MYR 1,240.00MYR 162.00MYR 87.00$ 162.00$ 112.00
15MYR 1,240.00MYR 1,260.00MYR 164.00MYR 89.00$ 164.00$ 114.00
16MYR 1,260.00MYR 1,280.00MYR 167.00MYR 90.00$ 167.00$ 116.00
17MYR 1,280.00MYR 1,300.00MYR 169.00MYR 91.00$ 169.00$ 117.00
18MYR 1,300.00MYR 1,320.00MYR 172.00MYR 93.00$ 172.00$ 119.00
19MYR 1,320.00MYR 1,340.00MYR 175.00MYR 94.00$ 175.00$ 121.00
Test
Cell Formulas
RangeFormula
H4H4=VLOOKUP(G4,$A:$F,3,TRUE)
I4I4=VLOOKUP(G4,$A:$F,4,TRUE)
J4J4=H4+I4
A5:A19A5=B4
B5:B19B5=A5+20
Named Ranges
NameRefers ToCells
_FilterDatabase=Test!$A$3:$F$352H4:I4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H4:I147Cell Value=0textNO
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows
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:
Rich (BB code):
    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:
Rich (BB code):
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:
Rich (BB code):
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,233
Messages
5,623,533
Members
415,976
Latest member
tuananh09x

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top