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.