I am having trouble changing a formula for another criteria. The formula is:
=IF(OR(DATEDIF(B2,"12/31/04","y")<21,E2<1000,D20),0,ROUND(IF(G2<=90000,11. 7%*G2,IF(G2<=200000,3%*90000+8.7%*G2,3%*90000+8.7% *200000)),2))
This calculates the profit share for each employee. I need to cap total
contributions for each employee at $40000. Columns a
Compensation G
Contribution to 401 H
Employer Share I
Profit Share J
Is there an easy way to adapt this formula in the same column (J)? Or do I
need to use K, and do another IF statement? I read that you can have 7
nested...but I keep getting ERROR, too many arguments when I try to add
another IF for the total of (H1+I1+J1)<=40000. I would like it to be the
profit sharing that is reduced if the total is over 40000, but again, if I
try to do it in another IF, I get the same ERROR message. Help, please!
Barbara

--
barbarat

If you want to cap it at 40000, how about

=MIN(40000,IF(OR(DATEDIF(B2,--"2004/12/31","y")<21,E2<1000,D20),0,ROUND(IF(
G2<=90000,11.7%*G2,IF(G2<=200000,3%*90000+8.7%*G2, 3%*90000+8.7%*200000)),2))
)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"barbarat" wrote in message
...
I am having trouble changing a formula for another criteria. The formula
is:

=IF(OR(DATEDIF(B2,"12/31/04","y")<21,E2<1000,D20),0,ROUND(IF(G2<=90000,11. 7
%*G2,IF(G2<=200000,3%*90000+8.7%*G2,3%*90000+8.7%* 200000)),2))
This calculates the profit share for each employee. I need to cap total
contributions for each employee at $40000. Columns a
Compensation G
Contribution to 401 H
Employer Share I
Profit Share J
Is there an easy way to adapt this formula in the same column (J)? Or do
I
need to use K, and do another IF statement? I read that you can have 7
nested...but I keep getting ERROR, too many arguments when I try to add
another IF for the total of (H1+I1+J1)<=40000. I would like it to be the
profit sharing that is reduced if the total is over 40000, but again, if I
try to do it in another IF, I get the same ERROR message. Help, please!
Barbara

--
barbarat

I think I tried that...and keep getting "too many arguments". help said 7...
I wll try it again. I am not proficient at this, so I may have entered it
incorrectly! I'll let you know.
Thanks for the suggestion.
Barbara
--
barbarat


"Bob Phillips" wrote:

If you want to cap it at 40000, how about

=MIN(40000,IF(OR(DATEDIF(B2,--"2004/12/31","y")<21,E2<1000,D20),0,ROUND(IF(
G2<=90000,11.7%*G2,IF(G2<=200000,3%*90000+8.7%*G2, 3%*90000+8.7%*200000)),2))
)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"barbarat" wrote in message
...
I am having trouble changing a formula for another criteria. The formula
is:

=IF(OR(DATEDIF(B2,"12/31/04","y")<21,E2<1000,D20),0,ROUND(IF(G2<=90000,11. 7
%*G2,IF(G2<=200000,3%*90000+8.7%*G2,3%*90000+8.7%* 200000)),2))
This calculates the profit share for each employee. I need to cap total
contributions for each employee at $40000. Columns a
Compensation G
Contribution to 401 H
Employer Share I
Profit Share J
Is there an easy way to adapt this formula in the same column (J)? Or do
I
need to use K, and do another IF statement? I read that you can have 7
nested...but I keep getting ERROR, too many arguments when I try to add
another IF for the total of (H1+I1+J1)<=40000. I would like it to be the
profit sharing that is reduced if the total is over 40000, but again, if I
try to do it in another IF, I get the same ERROR message. Help, please!
Barbara

--
barbarat

Bob:
That did not account for the total including column H, match...but I just
did another column, with a short IF stmt, which did it fine. Thanks so much
for your help!
Barbara
--
barbarat


"Bob Phillips" wrote:

If you want to cap it at 40000, how about

=MIN(40000,IF(OR(DATEDIF(B2,--"2004/12/31","y")<21,E2<1000,D20),0,ROUND(IF(
G2<=90000,11.7%*G2,IF(G2<=200000,3%*90000+8.7%*G2, 3%*90000+8.7%*200000)),2))
)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"barbarat" wrote in message
...
I am having trouble changing a formula for another criteria. The formula
is:

=IF(OR(DATEDIF(B2,"12/31/04","y")<21,E2<1000,D20),0,ROUND(IF(G2<=90000,11. 7
%*G2,IF(G2<=200000,3%*90000+8.7%*G2,3%*90000+8.7%* 200000)),2))
This calculates the profit share for each employee. I need to cap total
contributions for each employee at $40000. Columns a
Compensation G
Contribution to 401 H
Employer Share I
Profit Share J
Is there an easy way to adapt this formula in the same column (J)? Or do
I
need to use K, and do another IF statement? I read that you can have 7
nested...but I keep getting ERROR, too many arguments when I try to add
another IF for the total of (H1+I1+J1)<=40000. I would like it to be the
profit sharing that is reduced if the total is over 40000, but again, if I
try to do it in another IF, I get the same ERROR message. Help, please!
Barbara

--
barbarat