Help! 401k Match Formula

I am newer to Excel.

I need to create a formula that calculates an employer match and can be
modified to illustrate different employer matching amounts. It will need to
recognize what an employee is contributing, and match the appropriate amount
without going over.

I have two data columns, salary and percentage of salary employee is

Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

Similar Excel Tutorials

Return the Min or Max Value Using a Lookup in Excel - INDEX MATCH
Find the Min or Max value in a range and, based on that, return a value from another range. This is an advanced lo ...
Best Lookup Formula in Excel - Index and Match
A lookup using INDEX and MATCH is like a VLOOKUP without the restrictions.  Index and Match lookups offer you free ...
Count the Number of Cells that Contain Specific Text in Excel
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...
Vlookup Partial Match in Excel
Return Vlookup results on partial matches of a cell's contents. You could type the start, end, or middle of a looku ...

Helpful Excel Macros

Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Filter Data to Show the Top 10 Percent of the Data Set in Excel - AutoFilter
- This Excel macro filters a set of data in Excel to display only the top 10 percent of that data set. This means that th
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li

Similar Topics


Need help to correct my formula. I try using this formula in order to plot the contribution of the employer based on the salary of an employee:
=INDEX(contri table!$F$5:$F$33,MATCH($A4,TRIM(LEFT(contri table!$B$5:$B$33,FIND("-",contri table!$B$5:$B$33)-1))*1,1)) but it always gives me a #N/A result, what seems to be problem? Hope you could enlighten me bout this.

Thank you.

I have attendance sheet comprise on the following example:

Employee Name 1 to 31 column Formula Cell

Monday Tuesday Wednesday

Khan 10:00 10:05 10:10 ........ =
Khan-1 10:00 10:05 10:10 ........ =

As per company policy if any employee arrive after 10:00 then he or she must bear fine amount on month end.


if an employee came at 10:05 then the amount will be deducted 10.
if an employee came at 10:10 then the amount will be deducted 20.
if an employee came at 10:15 then the amount will be deducted 30.
so on

We need whole month deduction in one cell.

can anyone help me in this regard. If any confusion, do let me know.

Thanks in advance.

Hi All,

Appreciate your kind help to find a solution based on the below;

This is to calculate service indemnity for the employees as per the below conditions;

1- If the employee completes Five years or less then the employee is eligible for 15 days of salary for each year. However calculation will be (Gross Salary/ 26 X <no.of days worked>/365 X 15)

2- If the employee completes more than Five years of service, then the employee is eligible for one month salary for each additional year (after 5) till the last anniversary (Eg: joining date: 1-Oct-2000 and end/termination date: 13-Jul-2011, then his last anniversary will be 30-Sep-2010). In that case calculation will be (Gross Salary X 5) which means after the completion of 5 years employee is eligible for one month salary for each additional year till the last anniversary.

3- From the last anniversary till his end date then the calculation will be (Gross Salary/26 X <difference days between the last anniversay till employment end date> / 360 X 26)

I need a formula which includes all the above conditions, i'm trying IF conditions but stuck in between. Hope you clear what i'm looking for.

For easy reference please find the attached worksheet.

Thanks & Regards,

I have a table, consisting of 52 columns (52 weeks). In the table i want to update it with changes to employees salarys.

So each row will be a different employee.

eg. columnA= EMPLOYEE NAME, columnD=Week 1, columnE=Week2 etc.

When i change an employees salary i would enter the new salary into the relevant column.

What i want is a formula in columnB that will look along the row and return the value furthest to the right (ie. the most recently updated value).

Anyone help?

I want solution to automatize "Employee Salary Slips" by using MS word and MS Excel. I usually prepare an Excel list comprising all the employeeID's, employee name and their salary (split in 4 columns). I pass on this list to my assistant, who manually enter all the data one by one into Word template.

I want to automatize this, by using either Macros, VBA or mail-merge. I don't know whether this can be done or not.

If anyone can tell me if the problem can be solved, I'll do a bit of research and try to do it.

I'll be very gratefull if anyone can provide a sample. That would make my work more easier.

Thanking you in advance,


Hello All,
I am having issues combining two formulas:

=ADDRESS(5,MATCH("Employee: ",5:5))

The output of the address/match formula is $AA$5. I would like to replace the $AA$5 in the offset formula with the address/match formula like so:

=OFFSET(ADDRESS(5,MATCH("Employee: ",5:5)),0,2)

When I do this, excel says that the formula I typed contains an error. I have tried a number of different things with no luck. Any suggestions?

Thanks in advance

Can any body tell me how to calculate the salary in the given scenario in Microsoft Excel.

I have Date of Join, current date, and total for the date to know how many years and month he served, basic salary in the cells.

Now i need is
1. calculate the date.
2. every year employee salary will be increased in different term like (e.g. if he severed for 1 year 3% will be increase if 2 years then 4% like wise up to 10 years every year 1% will be increased. by 10 he will get 12% and after 10+ same 12% will be given.

i tried it so many times i don't what is wrong. any one know the solution.?

please help.

I need to develop a formula which will determine an employee's earnings for the fiscal year, November 1 - October 31, based on a prorated salary, in order to determine a bonus amount.

Example: Joe's salary is $50,000/yr and he started June 7, 2008. He would be entitled to a bonus based on prorated earnings for June to October 31(because he worked more than half of June)

However, Carol's salary is also $50,000/yr but she started after June 16. She would be entitled to a bonus based on prorated earnings for July to October 31 (because she worked less than half of June).

What formula would I use? How should the dates appear in the 'Start Date' Column?

We also have employees who started in previous fiscal years, how do I exclude them from the prorated calculation.

Thank you for your time.


Hi everyone! i've got a pretty mindboggling task ahead of me and i want to ask for your help.

I have this table which has employees names with basic pay and contribution.
I was tasked to create a excel sheet where in if the employees basic pay is input, the corresponding contribution for the employee will show depending on which salary bracket he/she is on.

Im not allowed to know what my workmates salaries are since im just an associate, so i just have to leave em blank and wait for my manager to input their salary.

please help me. im running out of ideas. please see attached file for the workbook

Attached please find a worksheet which was created by me through Google help. I am a beginner in Excel's Formulas.
I received a project from my boss to make an attendance sheet of each employee separately on monthly basis
but it's not only an attendance sheet it's a whole program to show the gross salary of an employee after many deductions
and additions according to employee salary as shown under:

1- Attendance time : 10:00 AM if employee come 1 or 2 hours late so these hours wages are deducted.

2- Overtime Charges (8 hours are working hours after that over time charges starts) we also have to add his overtime charges.

3- If employee gone before completion of 8 hours so the whole day wage is deducted from the month salary.

4- Sometime employee come to Sunday for extra work bundle so we have to include his overtime charges of Sunday.

5- Our boss and his partner had two companies i) ITC & ii) OCEX If ITC's employee gave his (Overtime / ITC's Working hours)
to OCEX 's outdoor work so these hours wages should pay by OCEX Admin and this rule is also applicable on ITC's Admin so we
have to show also that how many charges should pay by ITC & OCEX in the end of the month of this employee who works for both

In this sheet i made above 4 points but with troubles as under:

1- I used countif formula in E38 to count LATE in Column H and in F38 I multiply E38 (Late Days) to C2 (Day Wage)
to deduct day wages. Late applies on H Column if Working hours less than Regular 8 Hours if employee gives 8
hours or above 8 hours so the H column says NO means Day wage Safe but on Sunday when employee give overtime
so we don't need his 8 hours my format deduct his Late on Sunday also how can i save his Sunday Overtime.

2- In Date column which contains "Sun," wording we have to gave this row (R:255 G:124 B:128) color except H Column.

3- I am not able to add above point #5 in this sheet because after too much hardness i made this sheet please help?????

Shoaib Ali


Sorry if i can't explain it well. I am working on one excel sheet where i have following column,
gross salary deduction of advances amount due amount paid balance payable

i have more than one advances for each person in different months and i want to set them off against their last unpaid salary. e.g. the advance taken in sep,2013, oct, 2013 is now i have to adjust against january 2013 salary. (company pay salaries in arrears). please is some one can assist me in this. and salaries are being paid in different currencies.




I am creating a vacation accrual spreadsheet and I need assistance in the cells highlighted in green. The vacation accrual should reset itself automatically when the employee's anniversary date rolls around. The frequency of the accrual should be daily.

I have 2 different tabs. One for hourly employees and the other for salary. Both should work the same, with the exception of the amount of vacation time alotted hr. vs. salary.

I have the spreadsheet attached. I would like to only manually populate the cells highlighted in yellow and have the green ones automatically calculate.

Can you please help me to create the cells highlighted in green?

Thank you,

Creating a calendar to track vacation and sick time. Would like to use "V" for vacation and "S" for sick.

The first row contains my headers with my data starting in row 2:
A1 = employee no
B1 = employee name
C1 = days awarded
D1 = days used
E1 = days left

It's then followed by 12 for each month in the year: Jan, Feb, Mar, etc...

MONTHLY tabs...
On each monthly tab, the employee's number and names are listed downward starting with A2 being the header...

A2 = employee no
B2 = employee name

My dates run across from C2:AG2 in the following format: 01|02|03, etc... We would then mark a V or an S under the date and across an employees row to track his/her requests.

I need a formula on the TOTALS tab starting in D2 for my first employee to count how many "V"'s in all 12 tabs for the employee number listed in A2.

I can do a simple Countif, but I need it to look at and match the employee number from the Totals tab to all the monthly tabs and count the number of "V"s without the employee having to be on the same row, etc... The employees listed on Totals may not necessarily match the employees listed each month, and the Totals tab only lists the current employees, so being able to COUNT based on if the employee number matches that row, is important. I was thinking down the lines of a VLOOKUP, COUNTIF combo? But since I'm dealing with multiple tabs, I'm not sure how to pursue this.

Any sugestions for a formula to use? Your suggestions are HIGHLY appreciated and always a tremendous help!!!!

Could you also please give me a formula to use in Totals!D2 if I was calcualting based on hours instead of V and S? Instead of Days Awarded, it would be Hours Awarded etc... And I would use 8 instead of V for a full day's vacation request. Right now, this new manager is thinking that no one will request 1/2 a day, etc... so I would like to present both options to him.


I got a question that is stumping me right now.

I have to calculate some simple average and percentile data on salary. I have people who make the same salary, so I can do a straight line calculation of the percentile and average data. The data is presented like this.

Number of People Salary 2 10 4 20 6 30
I could list out the salary manually, but the data is much longer than the above example.


I have 2 columns of data with multiple rows

Column A
Row 1 - EE spouse

Column B
Row 1 - Employee Spouse

I want in Column C Row 1 to have the match formula say True or Yes even though the data is only a close match. I want to use a wildcard so the formula is like this:

in Column C
Row 1 =if(A1=*b1,"yes","no")

I want this comparison to happen row by row in column C, I don't want to conditional format Column A to equal Column B because i import the data from different systems.

I have in Excel a list of my employees by Name/Race/Gender/Birthdate/Hire date/Salary. There are a couple things I want to do with this information but I can't find out how to do it in the help file or online. I have basic excel 2007 knowledge but nothing advanced. What I want to do is:

1. Find employees whose salary is between 50000 and 65000. (I'm assuming I can use some kind of formula?)

2. Find employees who are female and hired before a certain date, for example 1/1/99.

3. Find the number of employees in each race and income group.

This is what my table looks like (I changed names for privacy):

Any help would be greatly appreciated.

I'm thinking its a conditional format but not totally sure. I could use some help here. Basically I have two columns. I'm trying to find the discrepancies (items non matching in column a). Those item that do not match side by side (i.e. a1 should match b1, a2 should match b2, a3 should match b3, etc.) should be denoted by a different color and/or highlight.

Thanks in advance!


I am creating a vacation accrual spreadsheet and I need assistance in the cells highlighted in green. The vacation accrual should reset itself automatically when the employee's anniversary date rolls around. The frequency of the accrual should be daily.

I have 2 different tabs. One for hourly employees and the other for salary. Both should work the same, with the exception of the amount of vacation time alotted hr. vs. salary.

I have the spreadsheet attached. I would like to only manually populate the cells highlighted in yellow and have the green ones automatically calculate.

Can you please help me to create the cells highlighted in green?

Thank you,

I know this might sound like a stupid question, please can someone help me with this?
I am trying to put a formula into a cell to give me the percentage remaining. eg if i have a total of 36 employees of different trades and 6 are off sick, what percentage do i have in to work? i know what % are off and can do this in a cell in excel, but for the life of me i can't find the formula to let me show how many employee's of the total i have in work that day. For an example lets say in cell G86 i have my total of 36 employee's into work, in cell J86 i have the total of 6 employee's off work, so how do i show in a cell the % of employee's available to work that day? thanks in advance


I am preapring a salary computation programme for my company. Now I am facing problem to calculate automatically CPF contribution payable and recoverable according to salary. I am looking for a VB programme or relevant formula to solve.

Please find attached file and advice me how to solve this calculation.


This is probably a very easy one but for the life of me I cannot achieve it in Excel 2007. Please can someone tell me how to plot a chart with multiple salary grade series on the Y axis plotted against an overall salary range on the x axis (see image)


All help gratefully received.


I have what I think is a fairly simple process I need to perform, but haven't been able to find the VBA code in my searches.

I would like to create a macro that will do the following:
I have 2 workbooks (WB1 and WB2). Both workbooks will have a variable amount of entries in column A. I would like the macro to loop through column A of WB1 and search for a match for that cell value in column A of WB2. If it finds a match, it will copy cells BU:CA from the row that contains the matching value in WB2 and use it to replace those same cells in WB1. If it does not find a matching value, I would like it to leave cells BU:CA of WB1 unchanged.

Thank you so much.

I have the following data:

A B C D E F G H 1 Machine Date Shift Activity First Name Last Name 2 A 21-Oct-10 Day Drill 3 B 21-Oct-10 Day Drill Mike Miller 4 A 21-Oct-10 Night Condition John Davidson 5 B 21-Oct-10 Night Condition Harry Benson 6 A 21-Oct-10 Day David Turner 7 A 21-Oct-10 Day Condition 8 B 21-Oct-10 Day Standby Mike Miller 9 B 21-Oct-10 Day Blast

What I would like to do is to have a formula that puts in the employee's name in each row in Column G and H. In the above data, you can see in Row 2, there is no employee name for Machine A that drilled on day shift on 21 Oct. However, in Row 6 where the activity is blank, there is employee name (David Turner) showing against Machine A on day shift on 21 October.

So I need excel to concatenate Columns A, B and C together, and then if that row has an existing employee name (eg Row 3 above), return the name in that row in Column G and H. If there is no name (eg Row 2 above), find any row that has an employee's name in it for the same machine, date and shift (eg Row 6), and then return that name in Column G and H.

My data is thousands of rows long, and each machine will have multiple activities for the same date and shift. Some of the activities will have names in Columns E and F, but most will not.

This one is too complicated for my little brain, so any help is greatly appreciated. Please let me know if I have not explained the problem clearly.

I have an excel sheet which has two columns- Employee ID and Number of hours worked on project. Employee IDs and Number of hours worked go like this-

ID Hours worked on project

101 .76
101 .33
102 .97
103 .77
103 .54
103 .65
103 .66
104 .87
104 .64

This goes on for 1830 ID values

I need a formula to calculate the total number of hours worked by each employee, for example Employee 101 has worked a total of 1.09 hours. Employee 102 has worked .97 hours. Employee 103 has worked 2.62 hours and so on for each employee. Each employee ID occurs multiple times (in many cases) because each employee works on several projects at the same time.

Please give me a formula, thank you very much for the help!


i am using excel 2003.I have a spreadsheet with over 8,000 rows that I need to match invoices and payments (positive and negative that net 0) with the same invoice number.

The invoice number is in column b, the amounts are in column d.
Currently, I am going down the list selecting cells and clearing the ones that match. There are for types of matches.

1. The ones that have both payments and invoices with the exact same amount (highlighted in sample data with green color).

2. The ones that have two payments against one invoice(or viceversa) with net amount of zero.(highlighted in sample data with orange color).

3. The ones that have one payment and two or more invoices, and the payment can be cleared with one of these invoices.(highlighted in sample data with blue color).

4. The ones that have both payments and invoices but amounts are different (highlighted in sample data withe green color).

Is there any way that we can flag matches, based on these four match types? (for example for match type one we will flag those rows with 1, similarly 2 for match type2, 3 for match type 3 and 4 for match typer for.)

Thank you so much.

Sample Data:

1944815 R235 invoice 123.63
1944815 R236 invoice 121.77
1944815 R235 payment (123.63)
1944815 R236 payment (121.77)
1944816 R237 invoice 123.09
1944816 R237 payment (100.05)
1944816 R237 payment (20.04)
1944817 R238 invoice 116.17
1944817 R238 invoice 116.17
1944817 R238 payment (116.17)
1944818 R239 invoice 761.19
1944818 R239 invoice (760.05 )

( Data starts from cell A2).