nested if statementss.

[pierre]

H11 = 172 500 000

i have in A1 : =IF(h11<=0,0,IF(H11-6000000>=0,6000000*2%," no tax"))


i have in A2 :
=IF(h11-6000000-9000000>=0,9000000*5%,IF(h11-6000000>=0,(H11-6000000)*5%,0))


i have in A3 :
=IF(H11-6000000-9000000-15000000>=0,15000000*7%,IF(H11-6000000-9000000>=0,(H11-6000000-9000000)*7%,0))


i have in A4 :
=IF(H11-6000000-9000000-15000000-30000000>=0,30000000*11%,IF(H11-6000000-9000000-15000000>=0,(H11-6000000-9000000-15000000)*11%,0))

i have in A5 :
=IF(H11-6000000-9000000-15000000-30000000-60000000>=0,60000000*15%,IF(H11-6000000-9000000-15000000-30000000>=0,(H11-6000000-9000000-15000000-30000000)*15%,0))

i have in A6 :
=IF(H11-6000000-9000000-15000000-30000000-60000000-120000000=0,1200000000,IF(H11-6000000-9000000-15000000-30000000-60000000>=0,(H11-6000000-9000000-15000000-30000000-60000000)*20%,0))

now guys , what should i do in order to make them in ONE SINGLE FORMULA in
cell B1 ??

FOR THE ONE WHO SOLVE THIS ONE I TELL HIM PREVIOUSLY : " RESPECT "

 

[bpeltzer]

You didn't state it, but I gather that the taxes computed in A1:A6 get added
together. So what you've got in each cell in column A is the tax applied
based on a marginal rate for a particular input range. This is very similar
to a common question posed here about calculating commissions with
accelerators.
Instead of all the IF statements, I generally create a table. The columns
in the table represent the minimum value for each range, the marginal rate
for that range, and the payout (or tax, in your case) due at the very low end
of the range. The first column MUST be the range breakpoints, and they MUST
be in increasing order. In your case, my table looks like:
Minimum, Rate, Tax at Threshold
0, 0%, 0
6M, 2%, 0
15M, 5%, 120k ...

(In subsequent rows, column C can be calculated. In the last row above, D3
would be =C3+B3*(A3-A2) )
Then if the income is in H11, the tax calculation is
=VLOOKUP(H11,A:C,3)+(H11-VLOOKUP(H11,A:A,1))*(VLOOKUP(H11,A:B,2)). In words,
that formula calculates the minimum tax for this income range, plus the
amount of income in this band multiplied by the marginal rate.

HTH. --Bruce