Better formula to compute Tax from Compensation other than nested IFs

Marcia · #1 10-29-2019, 07:14 AM

Hi everyone. This is my formula in computing the tax due from each employee based on the attached tax table. Is there a neater way of coming up with the same results without the so many nested IFs?
=IF(NetTaxInc>8000000,(2410000+0.35*(NetTaxInc-8000000)), IF(NetTaxInc>2000000,(490000+0.32*(NetTaxInc-2000000)),
IF(NetTaxInc>800000,(130000+0.3*(NetTaxInc-800000)),

IF(NetTaxInc>400000,(30000+0.25*(NetTaxInc-400000)),
IF(NetTaxInc>250000,(0.2*(NetTaxInc-250000))
IF(NetTaxInc<=250000,0))))))
Thank you. BTW the amounts are in Pesos currency not Dollars.

p45cal · #2 10-29-2019, 03:27 PM

I think I'm earning your money for you.

Cell C3 Formula (Image link)

Marcia · #3 10-30-2019, 08:28 AM

Quote:

Originally Posted by p45cal

I think I'm earning your money for you.

I wish

I have used index/match a few times but in very simple problems. I will now change the more than a decade old If/then formula to a smarter one. Thank you again.
Attached are the very happy beneficiaries of your generosity.

Steve_R · #4 12-16-2019, 05:37 PM

Incorporate Vlookup(). The link below explains it well
Tax Rates in Excel - Easy Excel Tutorial

Marcia · #5 12-17-2019, 03:26 PM

Quote:

Originally Posted by Steve_R

Incorporate Vlookup(). The link below explains it well

Thank you Steve. Vlookup would have been simpler to use but for the presence of the plus 30000, 140000 if taxable income is... in the formula.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
vlookup nested formula	paxon	Excel	1	09-14-2018 12:09 AM
nested IF formula question	H28Sailor	Excel	5	05-12-2017 11:01 PM
Nested If Statements in Formula Field to Produce the Diffrnc in Years Between Today's & Entered Date	mshu31	Word	1	02-09-2017 02:04 PM
Compute Time-Weighted Investment Return	billbowers	Excel	1	06-23-2016 08:30 AM
nested formula using IF & AND	paxon	Excel	3	02-18-2016 05:32 AM