Apply Tax Rate to Net Profit on P&L

[Guest]

Worksheet is named "ProfitLoss

A B C D E F
57 NET PROFIT (Before Tax) ($11,722) ($4,988) $13,582 $63,649 $47,684
58 % of Total Sales -55.49% -17.27% 25.29% 57.54% 50.78
59
60 INCOME TAX #VALUE! #VALUE! #VALUE! #VALUE! #VALUE

I need a formula that looks at the net profit (line 57) and chooses the correct income tax rate based on a table. Currently I have a table as follows

Worksheet is named "OPEX
A B C
202 No Less No Greater Ta
Than Than Rat
203 $0 $10,000 10
204 $10,000 $20,000 15
205 $20,000 $50,000 25
206 $50,000 $100,000 28
207 $100,000 $200,000 33
208 $200,000 $300,000 35
209 $300,000 $500,000 38

Although this is the style I chose for the tax table, it can be modified to accomodate ideas and/or formulas. I'm working on a deadline for Monday morning and REALLY appreciate any help

Thanks
Am

 

[Domenic]

Hi Amy,

Set up your tax table using two columns on your worksheet named OPEX.
Using Column A and B, and starting at Row 203, it would look something
like this:

0 10%
10,000 15%
20,000 25%
50,000 28%
100,000 33%
200,000 35%
300,000 38%

Then, on your ProfitLoss sheet, enter this formula in Cell B60 and copy
it across:

=IF(ISNA(VLOOKUP(B57,OPEX!$A$203:$B$209,2)),0,VLOOKUP(B57,OPEX!$A$203:$B$
209,2)*B57)

Hope this helps!

 

[Guest]

Domenic... you're awesome! I can't thank you enough.
God Bless!

 

[Domenic]

Domenic... you're awesome! I can't thank you enough.
God Bless!

You're very welcome, Amy!

Cheers!