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.

Attached Files

1-Sample Work Sheet for Macro Forums.xlsx (16.6 KB, 17 views)