Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 deferring. |
#2
|
|||
|
|||
Step One i would take would be to name your columns using the
Insert/Name/Define from the main tool bar. I would label them as EMPLOYEE, SALARY and DEFER (you will want the defer column formated as a %). Step Two would be to name a cell outside of the data area as LIMIT. Based on the above names, you could put the following formula in the next column (name this column (again, using the Insert/Name/Define from above) asMatch) for each row in the database: =if(DEFER<=LIMIT,DEFER,LIMIT) If you want the spreadsheet to then calculate the match in the next column, you would use the formula: =Salary*Match (this column you may want to name as USD_Match). HTH Chris Rogers |
#3
|
|||
|
|||
LIMIT is the amount your company is matching up to.
|
#4
|
|||
|
|||
Thank you Chris!!!
" wrote: Step One i would take would be to name your columns using the Insert/Name/Define from the main tool bar. I would label them as EMPLOYEE, SALARY and DEFER (you will want the defer column formated as a %). Step Two would be to name a cell outside of the data area as LIMIT. Based on the above names, you could put the following formula in the next column (name this column (again, using the Insert/Name/Define from above) asMatch) for each row in the database: =if(DEFER<=LIMIT,DEFER,LIMIT) If you want the spreadsheet to then calculate the match in the next column, you would use the formula: =Salary*Match (this column you may want to name as USD_Match). HTH Chris Rogers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help with Index, Match and Countif in the same complicated formula | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Offset & Match Formula Shows Duplicates | Excel Worksheet Functions | |||
how to build a formula to match numbers in 2 columns with the equ. | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions |