• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need formula to avoid circular references

K

Kumar@raja

Member
Dear Members,

Myself Kumar working as a Senior Finance Executive .This is my first post and I am very glad to be a member in this forum to hone excel skills. First and foremost I would like to convey my special thanks to Mr. Chandoo who has initiated and commenced this Forum. Thanks a lot Mr. Chandoo and looking for useful excel tips from your end.

Here is my query.

Let me know how can we remove circular reference in an excel sheet if the same containing formulas. I am aware that if want to remove the same need to click the excel options and click the formulas tab and enable err active calculations. However, I need a perfect formula for the same where can I get my desired result.

Your kind support in this regard would be greatly appreciated. Hereby attached an excel sheet for your ready reference.

Anticipating an early reply for the same.

Thank in advance,
Kumar
 

Attachments

  • Salary Structure .xls
    23.5 KB · Views: 48
Hi Kumar ,

See this file , and explain how exactly you want the calculations to be done.

Narayan
 

Attachments

  • Salary Structure .xls
    23 KB · Views: 64
@Kumar@raja Welcome to Chandoo.org forums and thanks for your question.

You can remove the circular reference by re-stating the equation for Special Allowance.

for your data, you can use =(C5-(1.1*SUM(C7:C10)+SUM(C15:C18)))/1.1 as the formula for this.

Explanation:

Assuming Y is CTC you want to offer.
A - Basic
B - HRA
C - TA
D - Medical Reimb
E - Special Allowance

F - Gratuity
G - PF
H - Employers ESI contri
I - Medical Insurance
J - Performance Incentive

X = A+B+C+D+E (signifies Total Earnings)
J = X/10

E = Y - (A+B+C+D+F+G+H+I+J)

Replacing definition of J, we get:

E = Y - (A+B+C+D+F+G+H+I+X/10)

Replacing definition of X, we get:

E = Y - (A+B+C+D+F+G+H+I+ (A+B+C+D+E)/10 )

Bringing E to the other side,

E + E/10 = Y - (1.1 *(A+B+C+D) + F+G+H+I)

1.1 E = Y - (1.1 *(A+B+C+D) + F+G+H+I)

So, E (special allowance) is,

E = (Y - (1.1 *(A+B+C+D) + F+G+H+I))/1.1

Refer to http://chandoo.org/wp/2010/09/16/excel-circular-references/ for help on circular references.
 
Dear Narayan,

Thank you very much for valued help. Really appreciated for your explanation .

Further, I have noticed an error in Employer ESI Contribution, actually it should be calculated @ 4.75% on Gross earnings (x @4.75%) . However the sheet figure towards same is incorrect. Hence, request you to kindly help me out for the same.

Over again thanks alot for the support.
 
Hi Kumar ,

I think you are referring to Chandoo's explanation.

Regarding your question , I have not understood what your calculations are supposed to be ; it would help iif you could clarify.

The fields , and their calculations are :
Code: 
BASIC SALARY                         40 % of the CTC
HRA                                  40 % of the Basic Salary
TRANSPORT ALLOWANCE                  Entered figure
MEDICAL REIMBURSEMENT                Entered figure
SPECIAL ALLOWANCE                    Calculated

Employer's Contribution:
Gratuity                                     4.81 % of the Basic Salary
Employer's Provident Fund Contribution       12 % of the Basic Salary
Employer's ESI Contribution                  Calculated
Medical Insurance                            Entered Figure
Performance Incentive                        10 % of the Gross Earnings
Can you confirm these , and explain how the fields labelled Calculated are arrived at ?

Narayan
 
Dear Narayan,

Thanks a lot for prompt response. The above calculations are correct. We are restructuring our Salary Breakup which was messed up earlier. In this regard, I was asked to prepare a permanent template, as I am looking after the Finance portfolio.

Our Revised CTC as Follows:

Monthly earnings
Basic @ 40% on CTC
HRA @ 40 % on Basic (non-metro)
Conveyance Rs. 800 P.M as per IT norms.
Medical Reimbursement Rs. 1250/- P.M
Special Allowance balance figure of = CTC-(Basic+HRA+Convey+Gratuity+Insurance+Employer P.F+ Performance Incentive+ESI Employer Contribution in case applicable)
All together is called Gross earrings

Employer Cost and Annual Compounds
Gratuity @ 4.81% on Basic
Employer PF @ 12% on Basic
Employer ESI @ 4.75% if Gross earnings >=15,000, if Gross earnings < 0
Insurance Rs.2000/- P.A which is fixed for everyone.
Performance Incentive based on the percentage it could be @ 0, 10, 15 or 20%.

In this regard I have attached an excel sheet for the same but could not fetch the desired result irrespective of circular reference. Hence, I need a correct formula like Mr. Chandoo has given above with clear description to fix the template as permanent. Please note that Incentive will be paid only few employees based on the offer letter therefore, few of them of the same is 0. Thus, there should have a provision , where we can indicate the percentage, so that excel compute the same automatically. Further, ESI Employer contribution should calculate @4.75% on Gross earnings which is C16 @4.75 % .

Ultimately wanted a temple which calculates the figures automatically and accurately once enter the CTC amount. Hence, request you to kindly help me out in this regard.

Once again thanks for the patience and support.

Thanks in advance,
Kumar
 

Attachments

  • Salary Structure Final.xls
    24 KB · Views: 45
@kumar... The big challenge as per your post is avoiding circular references. Once you paste my formula in your workbook, you can adjust rest of the calculations (like ESI changes etc.), the numbers would re-calculate and total would be same as CTC offered.

Try that and let us know if you have any problems.
 
Hi Kumar ,

The problem with your second uploaded file is that there are two circular references , where your first uploaded file had just one.

I have not tried to work out the calculations on the basis of your second file ; if we go by your first file , then the calculations can be done the way Chandoo explained. See this file and comment.

Narayan
 

Attachments

  • Salary Structure Final.xls
    25 KB · Views: 26
Dear Mr. Chandoo and Mr. Narayan,

Thanks a lot for taking time and your views. However, I could not fetch the results, as second file containing two circular references. Here, my second file is the final one and ESI calculations should be compute on Gross earnings @ 4.75% as employer contribution. Further, Performance Incentive would be vary from employee to employee, it could be 10% or 15% on Gross earnings. Truly, to get the logic for same is very difficult for me, as myself not good at Mathematics. However, Thanks a lot for your feedback and suggestions.

If you can provide the solution for the same at your convenience would be greatly appreciated.

Thanks in advance,
Kumar
 
Dear Chandu

Please find attached Salary slip format.

I have entered few formula and not able understand Special Allowance+Balancing figure in the attached file.

Please do the needful.

Regards
Kumar
 

Attachments

  • Salary slip- Need your help with formula.xlsx
    10.4 KB · Views: 18
Kumar@raja said:
Dear Mr. Chandoo and Mr. Narayan,

Thanks a lot for taking time and your views. However, I could not fetch the results, as second file containing two circular references. Here, my second file is the final one and ESI calculations should be compute on Gross earnings @ 4.75% as employer contribution. Further, Performance Incentive would be vary from employee to employee, it could be 10% or 15% on Gross earnings. Truly, to get the logic for same is very difficult for me, as myself not good at Mathematics. However, Thanks a lot for your feedback and suggestions.

If you can provide the solution for the same at your convenience would be greatly appreciated.

Thanks in advance,
Kumar
Click to expand...
 
Kumar Maratha said:
Dear Chandu

Please find attached Salary slip format.

I have entered few formula and not able understand Special Allowance+Balancing figure in the attached file.
Please do the needful.

Regards
Kumar
Click to expand...

Hello Kumar,

Can you try these formulas and check?

D2:
=IF(AND(B2<5000,B2>1000),800,1000)

E2:
=A2-SUM(B2:D2,G2:I2)

H2:
=IF(SUM(B2:D2)<10001,B2*4.75%,0)

Copy down all.

Regards,

PS:
I think you should start your own, rather than posting on someone's thread.
 
Hi Chandoo


I am getting error while executing below formula.
E2:
=A2-SUM(B2:D2,G2:I2)
Please do the needful.

Regards
Kumar

Khalid NGO said:
Hello Kumar,

Can you try these formulas and check?

D2:
=IF(AND(B2<5000,B2>1000),800,1000)

E2:
=A2-SUM(B2:D2,G2:I2)

H2:
=IF(SUM(B2:D2)<10001,B2*4.75%,0)

Copy down all.

Regards,

PS:
I think you should start your own, rather than posting on someone's thread.
Click to expand...
 
Kumar Maratha said:
Hi Chandoo


I am getting error while executing below formula.
E2:
=A2-SUM(B2:D2,G2:I2)
Please do the needful.

Regards
Kumar
Click to expand...
Dear what kind of error you are getting?

I didn't found any error, see the attached.
 

Attachments

  • Salary slip- Need your help with formula.xlsx
    9.9 KB · Views: 30
Kumar Maratha said:
Dear Khalid
Please find attached screen of the error.

Regards
Kumar
Click to expand...
Have you tried updating all formulas I posted?
Or have you checked the file in post # 14?

you have to change all formulas, not just one for column E.
Seems like you haven't checked the file i posted.
 
You must log in or register to reply here.
Back
Top