Return 0 After Milestone is Reached

[Patrick A]

All,

I am using this formula to calculate monthly taxes, which are based on
a % of income.

Once you've paid a certain amount of tax, you don't have to pay any
more.

I'm using this formula - which sucessfully works up to and including
the month where you pay less than the average month. I now need to
take it a step further, and say once the maxtax has been reached,
return a 0.

A4 to L4 are my monthly income amounts.

=IF(SUM($A4:L4)*tax<MaxTax,L4*tax,(SUM($A4:L4)*tax)-MaxTax)

Any suggestions?

Thanks,

Patrick

 

[Fred Smith]

Your problem is determining when Maxtax is reached. In the month where you
are going to go over the max, you still need to top up your tax payments. So
I don't think you can do it by looking at income alone. You need to check
your tax payments.

If your tax payments are in, say, row 5, use:
=IF(SUM($A5:K5>=MaxTax,0,IF(SUM($A4:L4)*tax<MaxTax,L4*tax,(SUM($A4:L4)*tax)-MaxTax))

Regards,
Fred

 

[Patrick A]

Thanks Fred!

 

[Fred Smith]

You're welcome. Thanks for the feedback.

Regards,
Fred