• 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


  • 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


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.


Salary: $30,000 (A2)

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

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



Is that you want



Excel Ninja
Staff member
If you want Tax to max at 8.6% of salary up to a minimum of $9000 tax per annum


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

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


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

and copy across


New Member
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.


Active Member
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


Excel Ninja
Staff member
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)))