From net salary to gross

Hello Everybody,

I am looking for a formula and am totally stuck. Please help...
I need to calculate the gross amount of salary of a list of employees that have a net salary guaranteed.

This is the data :
Gross - tax = net
Tax = ((taxable amount * tax rate) - deduction)
Taxable amount = (gross - 2000)
Tax rate = 5% if taxable amount is between 0.01 and 500; 10% if between 500.01 and 2000; 15% if between 20001.01 and 5000; 20% if between 5000.01 and 20000;...
Deduction = 0 if (taxable amount*tax rate) is between 0.01 and 500; 25 if between 500.01 and 2000; 125 if between 20001.01 and 5000; 375 if between 5000.01 and 20000;...

For the tax rate and the deduction I have been using Vlookups refering to the gross amount, but if I only know my net and have to look for my gross it does not work.

Hope it is clear and somebody can help

Blueocean84

Hi Dom,

Thanks for your quick message. I did and it is working. The problem is that I have a long listing of names and every month the gross will be different. I cannot imagine using goal seek one by one.

Is there any way I can transform goal seek in a formula?

Thanks again :-)
Blueocean84

Hello Dom

That macro would be totally adapted!
Can I create such a macro without vba? I have never used vba before.

Thanks again!

Hello Dom,

You are an Angel... :-)

I will look into the VBA tomorrow morning. I told to myslf many times already I had to start learning.

Enclosed you will find the actual file. I just started working on it, thus the months after oct are not applicable at all yet.

Basically the macro needs to run like this :
- If column C = "local" gross salary guaranteed, so nothing to do
- If column C = "international" the net salary is guaranteed, which means I need to find back my gross.
When I run my goal seek I will apply the following : (example line 13).
Set Cell : U13
to value : 0
by changing cell : G13.
My net( column W) + my tax (column T) will then be equal to my gross.

Which means I would like to run my goal seek for all lines for which column C is "international"

I think I have made the table more complicated than i should have been, but it is all I could come up with :-)

Thank you heeps!
Blueocean

Hi Squiggler47,

Thanks for the answer, however the right result does not appear.
I'm not sure I understand every step of your formula, but if I type for example 7779 in target salary, I should have a result of 8142 for the gross salary. With your formual I obtain same as target salary (7779). Did I misunderstand your explanation?

Btw... thank you so much for your input
blueocean84

Originally Posted by Domski

Here's a basic example using a macro to run goal seeks down a list of calculations. I know the calc isn't as complex as the one you're using but the theory is the same.

Dom

Hi Dom,

I really appreciate the way you tried to help blueocean. My problem is, when I put formulla in Tax coulmun instead of fixed value, the macro doesn't work . Can you fix this?

BR
Muhammad Javed