IFS

Copper Contributor

Hello,

I'm trying to create an equation that will calculate payroll taxes for scenario estimates on a Pro Forma.

I thought that I would use and IFS equation, but am not having any luck.

Basically, the equation needs to compute the taxes based on the amount of income.

From $0 to $132,900 the tax rate will be 15.3%, from greater than $132,900 up to and including $200,000 those earnings would be taxed at 9.1%, with any earnings over $200,000 taxed at 10.0% and then any or all of those taxes would be summed and show as a total in the cell.

 

Is this something that the IFS can do? Or am I better off trying this with nesting?

 

Thanks in advance for any help!

 

3 Replies

@bgrace 

If create such helper table

image.png

where on each next step % difference compare to previous step, formula could be

=SUMPRODUCT(($D$2-$A$2:$A$5)*$B$2:$B$5*($D$2-$A$2:$A$5>0))

@Sergei Baklan 

That will work. Sometimes a different perspective is needed. This simplified what was ending up a tedious and complicated IF or IFS statement.

Thanks!

@bgrace , you are welcome