# 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%&#62;SUM(\$B2:B2)*8.6%,B2*8.6%,9000*8.6%-SUM(\$B2:B2)*8.6%)

#### xld

##### Member
Is that you want

=IF(SUM(\$B2:B2)&#62;9000,0,IF(9000*8.6%&#62;SUM(\$B2:B2)*8.6%,B2*8.6%,9000*8.6%-SUM(\$B2:B2)*8.6%))

#### Hui

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

#### ptell8019

##### 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.

#### kchiba

##### 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)&#62;\$B\$5,\$B\$5,SUM(\$B\$4:B4)) =IF(SUM(\$B\$4:C4)&#62;\$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

#### Hui

##### Excel Ninja
Staff member
A4: =A3*8.6%

B4: =IF(SUM(\$A\$3:B3)&#60;9000,(SUM(\$A\$3:B3)*8.6%)-SUM(\$A\$5:A5),MAX(9000*8.6%-SUM(\$A\$5:A5,0)))

#### ptell8019

##### New Member
Thanks for your help! I will try them and see if I get the results I need.