Help with formula to canculate Post-tax vs. Pre-tax salary

  • Thread starter Thread starter Thuy
  • Start date Start date
T

Thuy

Dear all,

I would be grateful if you can help me with this.

I'm building the post-tax/pre-tax salary calculation. I have 2 columns
A & B. A is Post-tax and B is Pre-tax.
A
2,559
5,118
6,825
7,678
13,650
14,503
16,209
18,768
20,475
22,181
23,034
26,446

I have all the figures for the post-tax and want to build up a formula
so that pre-tax can be calculated automatically.

However, the formula depends on the amount of post-tax as follow:
From 5000 to 14000: (post-tax salary - 500)/0.9
From 14000 to 22000: (post-tax salary - 2000)/0.8
From 22000 to 32500: (post-tax salary - 4500)/0.7

I tried to use 'if' but really get stuck. Can somebody help me?

Thanks in advance,
Thuy
 
This is it, I think. Copy/paste into the formula bar. You will need t
insert = in the appropriate places to cover for amounts that equal th
cutoff values :-

=IF(AND(A1>5000,A1<14000),(A1-500)/0.9,IF(AND(A1>14000,A1<22000),(A1-2000)/0.8,IF(AND(A1>22000,A1<32500),(A1-4500)/0.7,0))
 
Here is a (possibly) simpler version. It cascades down rather tha
looking at intervals - so if the salary is less than x it looks at th
next tax bracket down rather than looking for salaries between y an
z.


'=IF(a1>=22000,+(a1-4500)/0.7,IF(a1>=14000,+(a1-2000)/0.8,IF(a1>=5000,+(a1-500)/0.9,+a1))
 
Thuy

Try this. I assume you have entitled column A1 as "A" and
B1 as "B" with the post tax figures in column A from cell
A2 downwards.

In cell B2 enter the formula

=IF(AND(A2>5000,A2<14000),(A2-500)/0.9,IF(AND
(A2>14000,A2<22000),(A2-2000)/0.8,IF(AND
(A2>22000,A2<32500),(A2-4500)/0.7)))

This will return the cell value 'FALSE' for B2. That is
because your first value of 2559 does not fall into any
category that you specify.

Now click and drag the formula down and it will calculate
your pre tax numbers as required.

Three important points to note:

(1) It would be worthwhile to check the results manually
just to ensure the formula is working. I did do this and
it seemed to be ok. Still I had only 13 data points to
work with so please check to be safe.

(2) There is a flaw in the above formula. If a value is
either 14000 or 22000 then the formula cannot
discriminate. You can solve this by amending the formula
such that, say, the first boundary if from 5000 to 13999
and then 14000 at 21999 and then 22000 to 32500. I do not
know how you want to deal with that problem (if it
arises) so I have left it up to you. For example, you may
elect to use 5000 to 14000 and then 14001 to 22000 and
then 22001 to 32500. So decide how you want to
discriminate the boundaries and then just type into the
formula manually.

(3) Disclaimer: To be sure, I am not an Excel expert so
please check the results. If there is a lot of
responsibilty in calculating tax figures then take care!

I will check back to see if you have any other problems.
I will be glad to assist.

Alex
 
try
=IF(F5>=22000,(F5-4500)/0.7,IF(F5>=14000,(F5-2000)/0.8,IF(F5>=5000,(F5-500)/
0.9,F5)))
 
Thank you all for your great help. I tried every single one and they all works.
For Alex's one, I just added ,A at the end of the formula then it's OK.

Thanks again,
Thuy
 
Back
Top