Tax help using vlookup and if function

Hello everyone

Here's the outline. I have been told to use this to calculate tax: When calculating the tax, assume that the monthly gross profit taxed @ 20% for the first $20,000 then 30% for the remainder of gross profit.

The monthly gross profit is $20,153 (C14) and the tax table (table7) is below:

[TABLE="width: 363"]

[tr]

[TD="align: center"]Taxable Income[/TD]
[TD="align: center"]Fixed Tax[/TD]
[TD="align: center"]Marginal Tax Rate[/TD]

[/tr]

[tr]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0.2[/TD]

[/tr]

[tr]

[TD="align: center"]20000[/TD]
[TD="align: center"]3999.8[/TD]
[TD="align: center"]0.3[/TD]

[/tr]

[/TABLE]

I don't know how to calculate the 20% of the first $20,000.

This is what I have done for the fixed rate: VLOOKUP(C14,Table7,2,TRUE) which gave me 3999.8

and for the 30% of the remainder ($153) I did: (C14-VLOOKUP(C14,Table7,1,TRUE))*VLOOKUP(C14,Table7,3,TRUE) which gave me 45.90

Is there a simpler way to do all this? And can IF function be used along with VLOOKUP at all? Thanks in advance

Faye

Additional info: I also need to allow the formula to show an operational loss (i.e. profit < $0), what does that mean?
Note: All formulae must contain VLOOKUP.

Re: Tax help using vlookup and if function

Hello jproffer

Thank you very much for the IF funtion statement. However, the formulas must be VLOOKUP alone or with other functions. I'm very sorry if my original post wasn't worded correctly. Can I use the IF function you provided within a VLOOKUP?

Re: Tax help using vlookup and if function

Yeah it is a flat 20% and then 30% but you have to use the vlookup to work it out.

Thanks for the help. It is much appreciated. The work is in the Profit/Loss sheet.

forum.ozgrid.com/index.php?attachment/44921/