• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF formula with maximum value

ptell8019

New Member
I'm trying to calculate tax for salary up to a certain amount on a month by month basis. I created a formula that will calculate the tax and the difference once it reaches that amount, but I'm having trouble figuring out how to make it enter a zero, instead of a credit amount, once it surpasses the maximum amount.


Example:

Salary: $30,000 (A2)

Mo. Salary: $2,500 (B2-M2)

Tax: 8.6% up to $9,000 of salary (row 4)


=IF(9000*8.6%>SUM($B2:B2)*8.6%,B2*8.6%,9000*8.6%-SUM($B2:B2)*8.6%)
 
Is that you want


=IF(SUM($B2:B2)>9000,0,IF(9000*8.6%>SUM($B2:B2)*8.6%,B2*8.6%,9000*8.6%-SUM($B2:B2)*8.6%))
 
If you want Tax to max at 8.6% of salary up to a minimum of $9000 tax per annum

use:

A4: =MIN(9000,SUM($A$3:A3)*8.6%)


If you want Tax at 8.6% of salary up to $9000 Income per annum

use:

A4: =MIN(9000,SUM($A$3:A3))*8.6%


and copy across
 
Thanks, but that formula gives me the total tax for the year at $9,000. I need to calculate the monthly tax up to $774 ($9,000 salary x 8.6%)


So Jan - Mar, should be $215 (ea. month) and April should be $129 (difference) and May - Dec should be at $0, since we already met the limit.
 
Try the following


The table below is from B1 to C7. Note how the dollars signs are in place to anchor column B in the formulas for Final Tax


Annual Sal 30000

Month Sal 2500 2500

Tax on Sal =8.6%*B3 =8.6%*C3

Max Tax =8.6%*9000

Final Tax =IF(SUM($B$4:B4)>$B$5,$B$5,SUM($B$4:B4)) =IF(SUM($B$4:C4)>$B$5,$B$5,SUM($B$4:C4))

Month Tax =+B6 =+C6-B6


The results are:


Annual Sal 30000

Month Sal 2500 2500 2500 2500 2500

Tax on Sal 215 215 215 215 215

Max Tax 774

Final Tax 215 430 645 774 774

Month Tax 215 215 215 129 0


Hope this helps
 
A4: =A3*8.6%

B4: =IF(SUM($A$3:B3)<9000,(SUM($A$3:B3)*8.6%)-SUM($A$5:A5),MAX(9000*8.6%-SUM($A$5:A5,0)))
 
Back
Top