Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 12th 18, 01:52 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2018
Posts: 1
Default Salary structure calculations

Dear Experts,

I need to design a macro w.r.t Salary Structure in excel as per following requirement.

Current Salary Structure is follows:-


S.No. Category Emp. Name Basic HRA Conv. Med. CCA SPA Take Home Bonus EPF ESIC CTC
1 1 RK 24,115 12,058 7,235 - 7,235 34,392 85,035 4823 2894 0 92,752
2 2 MSR 8,200 4,100 2,460 1,640 2,119 - 18,519 1640 984 787 21,930
3 2 DS 8,200 4,100 2,460 1,640 418 - 16,818 1640 984 715 20,157
4 1 NS 8,200 4,100 2,460 1,640 - 976 17,376 1640 984 738 20,738
5 2 RA 8,200 4,100 2,460 1,640 - 976 17,376 1640 984 738 20,738
6 1 EB 8,200 4,100 2,460 1,640 - 976 17,376 1640 984 738 20,738

I wish to keep new Salary Structure as follows:-

% Increment New CTC Gross Increase New Basic New HRA New Conv. New Med. New CCA New SPA New Take Home New Bonus New EPF New ESIC Total CTC
5% 97,390 4,638 25,321 12,661 7,596 5,064 7,596 31,048 89,287 5,064 3,039 - 97,390








Following should be the basis of Calculations: -
New CTC
"CTC" + "CTC"*%i Increment

Gross Increase
CTC - New CTC

New Basic
IF Category = 1 Then Max of 9200 OR 26% of New CTC
IF Category = 2 Then Max of 8200 OR 26% of New CTC
IF Category = 3 Then Max of 7800 OR 26% of New CTC

New HRA
IF((New CTC - (New Basic +New Bonus+New EPF + New ESIC))0
then
Min of ((New CTC - (New Basic + New Bonus+New EPF + New ESIC), 50% of New Basic)

New Conv.
IF((New CTC - (New Basic + New HRA+New Bonus+New EPF + New ESIC))0
then
Min of ((New CTC - (New Basic + New HRA+New Bonus+New EPF + New ESIC), 30% of New Basic)

New Med.
IF((New CTC - (New Basic + New HRA+New Conv. + New Bonus+New EPF + New ESIC))0
then
Min of ((New CTC - (New Basic + New HRA+New Conv. +New Bonus+New EPF + New ESIC), 20% of New Basic)

New CCA



IF((New CTC - (New Basic + New HRA+New Conv. + New Med. + New Bonus+New EPF + New ESIC))0
then
Min of ((New CTC - (New Basic + New HRA+New Conv. + New Med. +New Bonus+New EPF + New ESIC), 30% of New Basic)




New SPA
IF((New CTC - (New Basic + New HRA+New Conv. + New Med. + New CCA + New Bonus+New EPF + New ESIC))0
then
New CTC - (New Basic + New HRA+New Conv. + New Med. + New CCA+New Bonus+New EPF + New ESIC)


New Take Home
Sum of New Basic + New HRA + New Conv. + New Med. + New CCA + New SPA

New Bonus
20% of New Basic

New EPF
12% of New Basic

New ESIC
IF "New Take Home" < 21000
Then
"New Take Home"*4.25%
Else
0

Total CTC
Sum of New Take Home + New Bonus + New EPF + New ESIC

While applying formulas in excel it is giving circular error therefore need to have a macro. I wish of to enter only the % of Increment and the succeeded columns should get calculated automatically as per above logics specified. The Macro should get clicked/executed as soon as I enter/change the % of increment.
Hope I have clairified my requirement. Thanks for your cooperation in advance.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SORTING DATA IN TWO COLUMNS (salary and name ) - sorting as perdescending order of salary [email protected] Excel Programming 0 July 26th 13 09:53 AM
calculate diffence between old salary and new salary Felicia Pickett Excel Worksheet Functions 4 April 6th 10 04:32 PM
Salary by Qtr Formula KC Excel Discussion (Misc queries) 1 October 23rd 09 05:49 PM
salary tax calculation f lebasse[_2_] Excel Discussion (Misc queries) 2 August 21st 08 02:59 PM
Salary DP7 Excel Discussion (Misc queries) 2 February 23rd 07 09:09 PM


All times are GMT +1. The time now is 11:39 PM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017