"barbarat" wrote:
you gave me this, which did just what i wanted:
=IF(OR([...]),0,ROUND([...],2))
now, trying to add a limit to it that all contributions
(employee, match and profit share, cap at 40000,
and the reduction is in the profit share.

It really is easier (for me) to work with the MIN() form.
I believe the answer to you question is simply:

MIN(40000, ROUND(3%*MIN(G1,90000) + 8.7%*MIN(G1,200000), 2))

You could do something similar with the IF() solution.

That is just a cap on profit sharing. But with profit sharing,
there is (should be) no employee contribution or match.
So I might have misunderstood your question.

Are you really trying to refer back to the original pension
problem?

Are you trying to put a cap on the sum of the profit sharing
and pension benefits (employee's contribution and company's
match)?

That requires more changes to do it right. For example, H1
contained the employee contribution. You will need to add
MIN(40000,...) to that column. You had said that the
company match would go into I1. You will need to add
MIN(40000-H1,...) to that formula. Finally, you will need
to add MIN(40000-H1-I1,...) to the profit sharing formula.

Although I believe those changes ensure that the sum does
not exceed 40000, frankly I think it is strange. As I wrote
it, it allows the employee to contribute $40,000, which has
the effect of eliminating any company match or profit
sharing. Since there is usually a philosophy associated
with profit sharing at least (e.g, motivation), you probably
do not want to eliminate it completely simply because an
employee was over-zealous in the amount of his/her own
contribution to the 401(k).

Bottom line: perhaps we need a more precise statement
of what you intend with regard to these benefits in order
to give you a solution. You might also want to study
federal law on the subject, if you haven't already, since I
believe it addresses how some or all of these benefits
interact.