#1
|
|||
|
|||
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. Last edited by mpapreja; 05-09-2018 at 01:41 AM. Reason: Format converted to Text |
#2
|
||||
|
||||
Hi and welcome
please post a sample sheet with some data and expected results ( click Go advanced - Manage attachments)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Thanks for your quick response. File uploaded in original query.
|
#4
|
|||
|
|||
The problem is, that calculation p.e. of NewHRA contains reference to NewESIC, which contains reference to NewTakeHome, which contains reference to NewHRA.
So you are here like baron Von Münchausen who is trying to pull himself out of bog pulling his own hair. You can't calculate NewHRA before you know the value of NewHRA! |
#5
|
|||
|
|||
Somehow I can't edit my own post. I wanted to add, it is moot are you calculating new salaries using formulas or VBA - the problem is basic.
|
#6
|
|||
|
|||
Using For / Do Loop
Dear Arvilaanemets
I know the problem is there that’s why I am unable to implement excel’s standard formulas but I believe this can be achieved using macros. We take the simplest example as follows wherein the NewCTC is Rs. 15793 New CTC New Basic New HRA New Total as per Salary Sheet New Bonus New EPF New ESIC Total CTC 15,793 9,200 3,125 12,325 1,840 1,104 524 15,793 Since the following fields can be calculated straight forwardly therefore we will calculate them first New Basic (For Category 1) 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 Caculated as Rs. 9200/- New Bonus 20% of New Basic Calculated as Rs. 1840/- New EPF 12% of New Basic Calcuated as Rs. 1104/- Now there are 2 fields left (New HRA and New ESIC) which are creating circular references and are posing difficulties in calculation. We know that the total of all the above fields is Rs. 12,144/- and the HRA & ESIC to be calculated in such a way that in totality they should match the CTC value i.e. Rs. 15793/-. In above case the value of HRA is Rs. 3125/- and value of ESIC is Rs. 524/- so that in totality the CTC is coming out to be Rs. 15793/- If we can insert the calculation of NewHRA in a loop (by using various values) and thereafter we calculate the value of ESIC within loop only and check the total of all the calculated fields with NewCTC. If it matches we freeze the values of HRA and ESIC and exit the loop but if it doesnot in that case we again go to the start of the loop and again follow the above steps within loop untill we meet the required condition. if we are able to calculate the above values accurately we can consider remaining 5 cases in similar way. Hope I am clear and not mistaken. Please advice. |
#7
|
|||
|
|||
can somebody give me a macro only for calculating basic, hra, bonus and pf atleast?
|
#8
|
|||
|
|||
Quote:
I have achieved the desired results with following MACRO for the simplest case i.e. it is calculating Basic, HRA, Bonus, EPF, ESIC. I need to refine this code (make it more simple and efficient) and apply the same logic for calculating Conv., Med., CCA and SPA Values. There is one more problem, this code only works when i make changes in cell O8 (increment %) and press tab. if i use left, upper or down arrow the code doesnot work. the code should work as soon as there is a change in cell O8. Can somebody help me in this regard? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$O$8" Then Dim NewCTC As Double Dim GrossIncrease As Integer Dim NewBasic As Double Dim NewBonus As Double Dim EPF As Double Dim FinalCTC As Double Dim TakeHome As Double FinalCTC = 0 NewCTC = ActiveCell.Previous.Offset(0, -1).Value + (ActiveCell.Previous.Offset(0, -1).Value) * ActiveCell.Previous.Value GrossIncrease = NewCTC - ActiveCell.Previous.Offset(0, -1).Value ActiveCell.Previous.Offset(0, 1).Value = NewCTC ActiveCell.Previous.Offset(0, 2).Value = GrossIncrease 'New Basic Start Select Case ActiveCell.Previous.Offset(0, -13).Value Case 1 NewBasic = WorksheetFunction.Max(NewCTC * 0.26, 9200) Case 2 NewBasic = WorksheetFunction.Max(NewCTC * 0.26, 8200) Case 3 NewBasic = WorksheetFunction.Max(NewCTC * 0.26, 7800) End Select ActiveCell.Previous.Offset(0, 3).Value = NewBasic 'New Basic End 'New Bonus Start NewBonus = NewBasic * 0.2 ActiveCell.Previous.Offset(0, 10).Value = NewBonus 'New Bonus End 'New EPF Start NewEPF = NewBasic * 0.12 ActiveCell.Previous.Offset(0, 11).Value = NewEPF 'New EPF End 'FOR Freezing HRA Value - New HRA+ESIC Start NewHRA = NewBasic * 0.5 NewTakeHome = NewBasic + NewHRA If NewTakeHome < 21000 Then NewESIC = NewTakeHome * 0.0425 Else NewESIC = 0 End If FinalCTC = NewBasic + NewHRA + NewBonus + NewEPF + NewESIC If FinalCTC > NewCTC Then Do While (Round(FinalCTC, 0) <> Round(NewCTC, 0)) NewHRA = NewHRA - 1 NewTakeHome = NewBasic + NewHRA If NewTakeHome < 21000 Then NewESIC = NewTakeHome * 0.0425 Else NewESIC = 0 End If FinalCTC = NewBasic + NewHRA + NewBonus + NewEPF + NewESIC If NewHRA = 0 Then Exit Do End If Loop End If ActiveCell.Previous.Offset(0, 4).Value = Round(NewHRA, 0) ActiveCell.Previous.Offset(0, 12).Value = Round(NewESIC, 0) ActiveCell.Previous.Offset(0, 13).Value = Round(FinalCTC, 0) 'New HRA+ESIC End End If End Sub |
#9
|
|||
|
|||
Start by Using Option Explicit and declare the variables you use.
When using Worksheet_Change(ByVal Target As Range) use Target. You think what you've got works because you changed the value in cell O8 and then the Tab key took you to P8 where ActiveCell.Previous happens to be the Target. That's not the case when you use the left, upper or down arrow. Replace all those ActiveCell.Previous with Target. Writing values to the sheet will have this procedure calling itself over and over. Look into disabling and re-enabling Events to prevent this. Have a read of this. |
#10
|
|||
|
|||
Thanks for your valuable advice. I will try to implement your suggestion but can you please elaborate your following suggestion:-
"Writing values to the sheet will have this procedure calling itself over and over. Look into disabling and re-enabling Events to prevent this." I will definitely take care for the suggestions related to cross posting in future. |
Tags |
basic, salary, structure |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Document structure | PetrK | Word | 2 | 05-12-2017 07:25 AM |
Calculating the “total” based on “salary” value and its “type” (multiple IF), | Jamal NUMAN | Excel | 4 | 07-05-2016 12:34 AM |
Structure chart | jenny p | PowerPoint | 1 | 02-24-2016 09:14 AM |
VLookup formula Problem (salary plus (commission x sales)) | topgear2015 | Excel | 11 | 06-05-2013 09:02 PM |
How to add more tel nos to contacts structure | plainman007 | Outlook | 0 | 03-16-2010 02:16 PM |