Need Help to fix formulas prepare wage Book

[Ronald1969]

Hello, 

Can some help me with fixing up some formula to prepare wage book.

I have to pay employees certain amount as net payment after all statutory deduction.

In the wage book, certain rates cannot be changed like Basic, HRA, VDA. However, Allowance can be paid more to arrive at the final desired net payment.

I have attached a table in excel sheet. I will be grateful if someone can please help me.

[#2]

@Ronald1969 

Hi again! Your problem is quite tricky as the allowance is part of the total which in turn is the basis for the ESIC deduction. You would have to use "goal seek" for each and every employee to let Excel calculate the allowance for each so that the total net becomes 400 per day. I guess that with VBA you can automate the goal seek, but I haven't gone so far. The attached file now includes a formula for the Allowance, generating an error of max 1, when rounding the outcome to 0 decimals. Up to you to decide if that's acceptable.

[#3]

Sir,

You have been a great help. you have simplified the process and that is very helpful for me in my work.

Thank you once again and God Bless.

Ronald Pinto