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. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
