Remember Me?

#### Menu

#1
May 12th 18, 01:52 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: May 2018 Posts: 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.

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post [email protected] Excel Programming 0 July 26th 13 09:53 AM Felicia Pickett Excel Worksheet Functions 4 April 6th 10 04:32 PM KC Excel Discussion (Misc queries) 1 October 23rd 09 05:49 PM lebasse[_2_] Excel Discussion (Misc queries) 2 August 21st 08 02:59 PM DP7 Excel Discussion (Misc queries) 2 February 23rd 07 10:09 PM

All times are GMT +1. The time now is 07:40 PM.

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

# About Us

"It's about Microsoft Excel"

Copyright © 2017