Salary Deductions
I have created a Employee Master data. I need to the formula in the below mentioned way
1) If the employee works more than 1 yr in the company, there should be an Increment in his Gross Salary of 50% ( In which 80% should be in Basic & 20% in Allowances)
2) If the employee works more than 6 months, Increment in his Gross Salary of 25% (In which 80% should be in Basic & 20% in Allowances)
Looking for your Assistance. Please in need of your Valuable Help!!
Thanks,
Fareed
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Best Regards,
Kaper
This very basic workbook might give you some ideas to work with. If there is a change in the basic salary like a promotion, then a new row would be entered for the employee with the new basic salary entered but all the other data remaining the same.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Re: Salary Deductions
Hi Newdoverman,
Thanks for your Assistance. Herewith I'm attaching the Excel Sheet. I have followed the Ideas which you have provided in the Salary Idea Worksheet.
Now I need to know Whether the Deductions happens in the Same Way?
1) If there is Less than 2 Years of Service, then the Gross salary deductions should be 5%(In which Basic 80% and Special Allowance 20%)
2) If less than 1 year of service, Gross salary deduction should be 3% (In which basic 80% and SA 20%)
3) If less than 6 months of service, Gross salary deduction should be 1.5% (In which basic 80% and SA 20%)
I'm a Newbie to Excel, I want to Explore more in Excel? What are the ways to develop my Excel Skills? Need your Valuable Advise.
Thanks,
Fareed
Hi Kaper,
I have attached the Workbook. Please do the Needful
Thanks,
Fareed
How about this:
Quang PT
Hi Bebo,
Thanks for the Calculation. But I need to know how the concept works ? I thought with the help of If function alone we can do the Calculation. Where I can learn those formulas In depth?
Regards,
Fareed
1) First we need to find out what dates are 24,12,6 months counted from the past of today's date:
EDATE(TODAY(),-{24,12,6})
with today = 28/2/2014, they are {41333,41698,41879} = {28/2/2013,28/2/2014,28/8/2014}
2)Then use LOOKUP to locate D2 position in deduction list:
LOOKUP(D2,EDATE(TODAY(),-{24,12,6}),{0.05,0.03,0.015})
with D2=27/11/2014, evaluate:
LOOKUP(D2,{28/2/2013,28/2/2014,28/8/2014},{0.05,0.03,0.015})
results: 0.03
3)define total amount of deduction: Gross salary * deduction rate:
K2*LOOKUP(D2,EDATE(TODAY(),-{24,12,6}),{0.05,0.03,0.015})
4)Basic salary is deducted 80% on total amount of deduction
K2*LOOKUP(D2,EDATE(TODAY(),-{24,12,6}),{0.05,0.03,0.015})*0.8
5) New basic salary after deduction:
H2-K2*LOOKUP(D2,EDATE(TODAY(),-{24,12,6}),{0.05,0.03,0.015})*0.8
6)For employees who is over 2 years pass, it should returns unchanged:
=IFERROR(H2-K2*LOOKUP(D2,EDATE(TODAY(),-{24,12,6}),{0.05,0.03,0.015})*0.8,H2)
That is how my formular works. I wonder if the final results is correct?
I'm looking on your Answer. If doubt I will come back to you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks