I am trying to understand the differences in how i wrote the IFs; you gave me
this, which did just what i wanted:
=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))
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. since
this formula is in the column (H) for the profit share, can it be amended to
do the reduction? Or do I make a new column, add this result to match
(column I), and limit that to 40000?
sorry, this is all very new to me. thanks so much for your assistance,
Barbara
--
barbarat


"barbarat" wrote:

that worked perfectly! and is much shorter than my sad attempt! after
comparing my formula to yours, i see that i am unsure of commas, and )'s.
and the rules for what comes first...is there a rule i could learn, and if so
where do i look for it? meaning, what should i reference in the manual
index? the date thing really confuses me. if i wanted to use numerical,
like 01011984 instead of mm/dd/yyyy, what would i need to change? thanks so
much for your help.
--
barbarat


" wrote:

"barbarat" wrote:
so, what if i wanted to do a similar calc; a profit sharing
based on 3% of compensation up to 90,000 plus 8.7% of
all compensation? no contribution is allocated above $200000

If I understand correctly, that could be written:

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

That rounds to cents. Replace ",2" with ",0" to round to dollars.

On 12/31/04 they must be 21 yrs old, have worked 1000 hrs
for that year and still be employed with the company .
would something like this work: hrs worked is column J,
and terminated Y/N is K:

=IF(DATEDIF(B1,"12/31/2004","y")<21,0,ANDIF(J1=1000),0,[...]

I assume you mean "at least 21 yr old" and "at least 1000 hr".
That could be written:

IF(OR(DATEDIF(B1,"12/31/2004","y")<21, J1<1000, K1="Y"), 0,
ROUND(3%*MIN(G1,90000) + 8.7%*MIN(G1,200000), 2))

or equivalently:

IF(AND(DATEDIF(B1,"12/31/2004","y")=21, J1=1000, K1="N"),
ROUND(3%*MIN(G1,90000) + 8.7%*MIN(G1,200000), 2), 0)

=IF(DATEDIF(B1,"12/31/2004","y")<21,0,ANDIF(J1=1000),0,IF(G1=<90000,
(G1*.03+G1*.087)OR(IF(G190000,90000*.03+G1*.087)A ND(IFG1=2000000,
(90000*.03+200000*.087),0)))

please...no laughing. i would love to find out where in this formula
i've gone wrong.

Lots of syntax errors, too many to comment individually.
But there is no harm in wanting to use IF() functions, if
that makes the logic clearer to you. I might write:

IF(OR(DATEDIF(B1,"12/31/2004","y")<21, J1<1000, K1="Y"), 0,
ROUND(IF(G1 <= 90000, 11.7%*G1,
IF(G1 <= 200000, 3%*90000 + 8.7%*G1,
3%*90000 + 8.7%*200000)), 2))