Help, please...I am using Excel 2003...as a newbie...
I need to calculate (in Column I) the match for a 401(k) as follows:
Column G is compensation, H is employee contribution, and B is Date of birth
(as numbers...ie, 01011986).
The match is $1 for each dollar invested up to 3% of compensation, and $.75
for each dollar invested over 3%, but not greater than 6%, of compensation,
and $.50 match for each dollar between 7 and 10%. No match over 10%. Also
no match if employee is under 18 yrs of age at 12/31/04.
I have tried IF...and keep gettong the wrong results.
Any help will be greatly appreciated. Thank you.
--
barbarat

"barbarat" wrote:
I need to calculate (in Column I) the match for a 401(k) as follows:
Column G is compensation, H is employee contribution, and B is Date
of birth (as numbers...ie, 01011986).
The match is $1 for each dollar invested up to 3% of compensation,
and $.75 for each dollar invested over 3%, but not greater than 6%,
of compensation, and $.50 match for each dollar between 7 and 10%.
No match over 10%. Also no match if employee is under 18 yrs of
age at 12/31/04.
I have tried IF...and keep gettong the wrong results.

I assume you mean "not greater than 7%" or "between
6 and 10%". Otherwise, answer: what is the match for
the amount between 6% and 7% of compensation?

Ostensibly:

IF(DATEDIF(B1, "12/31/2004", "y") < 18, 0,
INT(MIN(H1, G1*3%))
+ 0.75*MAX(0, INT(MIN(H1-G1*3%, G1*6%)))
+ 0.50*MAX(0, INT(MIN(H1-G1*6%, G1*10%))))

This assumes that no birthday is after 12/31/2004. It also
assumes that B1 is the form of mm/dd/yyyy. Yours is in
the form mmddyyyy. Ideally, change the format in B1.
Otherwise, replace B1 above with the following formula
or a reference to a helper cell with it:

DATEVALUE(INT(B1/1000000)
& "/" & INT(MOD(B1,1000000)/10000)
& "/" & MOD(B1,10000))

Errata ....

I wrote:
IF(DATEDIF(B1, "12/31/2004", "y") < 18, 0,
INT(MIN(H1, G1*3%))
+ 0.75*MAX(0, INT(MIN(H1-G1*3%, G1*6%)))
+ 0.50*MAX(0, INT(MIN(H1-G1*6%, G1*10%))))

Should be:

IF(DATEDIF(B1, "12/31/2004", "y") < 18, 0,
INT(MIN(H1, G1*3%))
+ 0.75*MAX(0, INT(MIN(H1, G1*6%) - G1*3%))
+ 0.50*MAX(0, INT(MIN(H1, G1*10%) - G1*6%)))

I will try this tonight...thanks so much! I'll let you know whether it is
successful.
--
barbarat


" wrote:

Errata ....

I wrote:
IF(DATEDIF(B1, "12/31/2004", "y") < 18, 0,
INT(MIN(H1, G1*3%))
+ 0.75*MAX(0, INT(MIN(H1-G1*3%, G1*6%)))
+ 0.50*MAX(0, INT(MIN(H1-G1*6%, G1*10%))))

Should be:

IF(DATEDIF(B1, "12/31/2004", "y") < 18, 0,
INT(MIN(H1, G1*3%))
+ 0.75*MAX(0, INT(MIN(H1, G1*6%) - G1*3%))
+ 0.50*MAX(0, INT(MIN(H1, G1*10%) - G1*6%)))

IT WORKED!! Thank you so much. This board is wonderful; I will use it often
as a resource as I learn Excel. Perhaps I will learn enough so that someday
it will be I who can answer a question! thank you again.
--
barbarat


"barbarat" wrote:

I will try this tonight...thanks so much! I'll let you know whether it is
successful.
--
barbarat


" wrote:

Errata ....

I wrote:
IF(DATEDIF(B1, "12/31/2004", "y") < 18, 0,
INT(MIN(H1, G1*3%))
+ 0.75*MAX(0, INT(MIN(H1-G1*3%, G1*6%)))
+ 0.50*MAX(0, INT(MIN(H1-G1*6%, G1*10%))))

Should be:

IF(DATEDIF(B1, "12/31/2004", "y") < 18, 0,
INT(MIN(H1, G1*3%))
+ 0.75*MAX(0, INT(MIN(H1, G1*6%) - G1*3%))
+ 0.50*MAX(0, INT(MIN(H1, G1*10%) - G1*6%)))

"barbarat" wrote:
IT WORKED!! Thank you so much.

Glad to hear that. Thanks for letting me know.

Is the formula clear, or do you want some explanation?

Hello again...yes, I think I understand it. The INT is for rounding, right?
And the MIN/MAX I get, now! I was trying to find a way to meet the
constraints, and this did it perfectly. I knew an IF would do it in one
column if designed correctly. I am thinking of a new column, for profit
sharing...but need to formulate in my head so I am clear. I will try posting
what I think the formula needs to be, at the risk of sounding confused, when
I have a better handle on it. I learned much from looking at the difference
in my formula (which was pathetic), and yours! Thanks again.
--
barbarat


" wrote:

"barbarat" wrote:
IT WORKED!! Thank you so much.

Glad to hear that. Thanks for letting me know.

Is the formula clear, or do you want some explanation?

"barbarat" wrote:
Hello again...yes, I think I understand it.

Great.

The INT is for rounding, right?

Well, for truncating. It will match $1 to $1 through $1.99.
If you want to round (match $2 to $1.50 through $2.49),
change INT(...) to ROUND(...,0). If you want to match $2
to $1.01 through $2 -- i.e, to any dollar or part thereof --
use ROUNDUP(...,0) or CEILING(...,1).

got it...that is going to come in really handy for spreadsheets in the
future. I had an IF statement a mile long, and it would not work!
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, but there are really no employees at that level,
anyway.
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,IF(G1=<90000,(G1 *.03+G1*.087)OR(IF(G190000,90000*.03+G1*.087)AND( IFG1=2000000,(90000*.03+200000*.087),0)))

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


" wrote:

"barbarat" wrote:
Hello again...yes, I think I understand it.

Great.

The INT is for rounding, right?

Well, for truncating. It will match $1 to $1 through $1.99.
If you want to round (match $2 to $1.50 through $2.49),
change INT(...) to ROUND(...,0). If you want to match $2
to $1.01 through $2 -- i.e, to any dollar or part thereof --
use ROUNDUP(...,0) or CEILING(...,1).

It is not truncating, check with a negative value

--
Regards,

Peo Sjoblom

(No private emails please)


" wrote
in message ...
"barbarat" wrote:
Hello again...yes, I think I understand it.

Great.

The INT is for rounding, right?

Well, for truncating. It will match $1 to $1 through $1.99.
If you want to round (match $2 to $1.50 through $2.49),
change INT(...) to ROUND(...,0). If you want to match $2
to $1.01 through $2 -- i.e, to any dollar or part thereof --
use ROUNDUP(...,0) or CEILING(...,1).

what does that mean, please? are you referring to the first formula, and the
INT, or something about the second formula that I need to correct...well, i
know it needs correcting; it doesn't work. help please thanks!
--
barbarat


"Peo Sjoblom" wrote:

It is not truncating, check with a negative value

--
Regards,

Peo Sjoblom

(No private emails please)


" wrote
in message ...
"barbarat" wrote:
Hello again...yes, I think I understand it.

Great.

The INT is for rounding, right?

Well, for truncating. It will match $1 to $1 through $1.99.
If you want to round (match $2 to $1.50 through $2.49),
change INT(...) to ROUND(...,0). If you want to match $2
to $1.01 through $2 -- i.e, to any dollar or part thereof --
use ROUNDUP(...,0) or CEILING(...,1).

I meant with regards, to the post saying "Well, for truncating"
it is incorrect, INT rounds down to the nearest integer, TRUNC truncates

5.5
-5.5

INT returns

5
-6

while TRUNC

returns

5
-5


my post was not in response to your post

having said that without checking your formula if you want to use AND you
use it as

=IF(AND(DATEDIF(B1,"12/31/2004","y")<21,J1=1000),0,etc

meaning that if younger than 21 and if J1 is greater or equal to 1000, then
return 0, else do this or that

--
Regards,

Peo Sjoblom

(No private emails please)


"barbarat" wrote in message
...
what does that mean, please? are you referring to the first formula, and
the
INT, or something about the second formula that I need to correct...well,
i
know it needs correcting; it doesn't work. help please thanks!
--
barbarat


"Peo Sjoblom" wrote:

It is not truncating, check with a negative value

--
Regards,

Peo Sjoblom

(No private emails please)


"
wrote
in message ...
"barbarat" wrote:
Hello again...yes, I think I understand it.

Great.

The INT is for rounding, right?

Well, for truncating. It will match $1 to $1 through $1.99.
If you want to round (match $2 to $1.50 through $2.49),
change INT(...) to ROUND(...,0). If you want to match $2
to $1.01 through $2 -- i.e, to any dollar or part thereof --
use ROUNDUP(...,0) or CEILING(...,1).

:
can you explain CEILING to me, please? I am trying to do the second
formula, and since the limit on compensation is 200000, thought ths would
work...it doesn't.:

IF(AND(DATEDIF(B1,"12/31,2004","y")<21,J1=1000),0,IF(OR(K1=Y,IF(AND(G1< =90000),0,INT(MIN(G1*.03+G!.087))OR(IF(G1=90000), CEILING(g1=200000)MAX(G1*.03*+G1*.087),0))))

--
barbarat


"barbarat" wrote:

got it...that is going to come in really handy for spreadsheets in the
future. I had an IF statement a mile long, and it would not work!
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, but there are really no employees at that level,
anyway.
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,IF(G1=<90000,(G1 *.03+G1*.087)OR(IF(G190000,90000*.03+G1*.087)AND( IFG1=2000000,(90000*.03+200000*.087),0)))

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


" wrote:

"barbarat" wrote:
Hello again...yes, I think I understand it.

Great.

The INT is for rounding, right?

Well, for truncating. It will match $1 to $1 through $1.99.
If you want to round (match $2 to $1.50 through $2.49),
change INT(...) to ROUND(...,0). If you want to match $2
to $1.01 through $2 -- i.e, to any dollar or part thereof --
use ROUNDUP(...,0) or CEILING(...,1).

okay...so i tried changig the format in the Date of termination, instead of
Terminated, Y or N, I put 0 if not, and a date (numerical) if yes. still
can't make it work. I think I have too many ifs...it keeps saying "to many
arguments", but thought we could use 7?? help, please?
conditions again:
employee must not be terminated on 12/31/04 (now column D), be 21 (DOB
column B), worked 1000 hrs (column E), be employed for one year (date of hire
is column C) and then up to 90000 is a profit share match of 3%, plus 8.7% of
total compensation. no share over 200000 of compensation.
I changed my formerly posted formula (which did not work) to numerical for
date of termination, and used Ceiling to try to force the 200000 limit. I
have tried adding commas, removing commas, adding/removing
parentheses...help, please! thanks.
--
barbarat


"barbarat" wrote:

got it...that is going to come in really handy for spreadsheets in the
future. I had an IF statement a mile long, and it would not work!
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, but there are really no employees at that level,
anyway.
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,IF(G1=<90000,(G1 *.03+G1*.087)OR(IF(G190000,90000*.03+G1*.087)AND( IFG1=2000000,(90000*.03+200000*.087),0)))

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


" wrote:

"barbarat" wrote:
Hello again...yes, I think I understand it.

Great.

The INT is for rounding, right?

Well, for truncating. It will match $1 to $1 through $1.99.
If you want to round (match $2 to $1.50 through $2.49),
change INT(...) to ROUND(...,0). If you want to match $2
to $1.01 through $2 -- i.e, to any dollar or part thereof --
use ROUNDUP(...,0) or CEILING(...,1).

"Peo Sjoblom" wrote:
I meant with regards, to the post saying "Well, for truncating"
it is incorrect, INT rounds down to the nearest integer,
TRUNC truncates

Technically correct. My comment was made in context.
The discussion is about compensation etc, which is always
positive. The OP said that INT() would "round". I said it
"truncates". Arguably, I should have said "rounds down".
I was tempted to write that, but I thought "truncate" might
be clearer, since it is the same thing for positive numbers.

Having said all that, I will try to be more careful in the future,
since "int" does mean truncate (the unsigned magnitude)
in some programming languages.

i understood that the INT worked to rund because it was positive. i should
have stated that i went to the manual, and under help, so i got what you
said.
can you help me with the second part?
also, i had a thought. on the first formula, could i have used a separate
sheet, then filtered out the no's, then done a formula that referenced the
prior sheet? or is that more complicated (it's all complicated to me!)
the second one, i keep getting error messages, and when i try to get out of
the ell, lose what i have entered...help, please
--
barbarat


" wrote:

"Peo Sjoblom" wrote:
I meant with regards, to the post saying "Well, for truncating"
it is incorrect, INT rounds down to the nearest integer,
TRUNC truncates

Technically correct. My comment was made in context.
The discussion is about compensation etc, which is always
positive. The OP said that INT() would "round". I said it
"truncates". Arguably, I should have said "rounds down".
I was tempted to write that, but I thought "truncate" might
be clearer, since it is the same thing for positive numbers.

Having said all that, I will try to be more careful in the future,
since "int" does mean truncate (the unsigned magnitude)
in some programming languages.

"barbarat" wrote:
can you explain CEILING to me, please?

Forget about Excel's CEILING() function. It is poorly
defined, in my opinion. Besides, I really did mean
ROUNDDOWN(...,0), even for negative numbers,
although that should not be an issue for you.

I am trying to do the second formula, and since the
limit on compensation is 200000, thought ths would
work...it doesn't.:

IF([...] CEILING(g1=200000)MAX(G1*.03*+G1*.087),0))))

I am not exactly sure what you are trying to write. The
above is syntactically incorrect. Even if you meant:

CEILING(g1=200000,1)*MAX(...)

that is non-sensical. CEILING(x,1), in this case, returns the
largest integer value of x rounded up, away from zero. The
expression "g1 = 200000" is a boolean (true, false), not a
number that we would normally apply CEILING() to.

"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))

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))

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))

"barbarat" wrote:
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?

I doubt that your problem is with understanding "commas
and parentheses [and] what comes first". But if that truly
is your problem, the best thing is to rely on the Help text
for each function. I find the "offline" text better than the
online text because of the See Also link. If you agree, click
on Help, then click on Online Content Settings in the Help
window. Uncheck "Content and links from Microsoft Office
Online". After clicking Okay, you will have to exit and
re-enter Excel to see the effect.

The See Also link might help you with what I suspect is the
real problem: how to know what functions and operators
exist. If you look at the See Also link on the Help text for
the "IF worksheet function", you will discover the AND() and
OR() functions, for example. Careful reading of the syntax
and a general knowledge about how function arguments
and expressions work in programming languages will help
you get it right.

Sorry, but I don't know of any other magic way to learn a
"language" -- which Excel expressions are -- other than
finding a good intro book.

the date thing really confuses me.

Yes, it took me some time to remember DATEDIF() myself.
I did not find any Excel documentation. I found it with a
google search.

if i wanted to use numerical, like 01011984 instead of
mm/dd/yyyy, what would i need to change?

I believe I answered that in an earlier posting in this thread.
Look for it. It isn't pretty. Why do you prefer the "numerical"
format? What problem does that solve for you that the
normal format does not?

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

Hi again:
The only way to approach is at the reducing profit sharing. Employees are
limited to 14000 contribution (15000 as of next year) by IRS regs. Our match
can be as generous as we like...1%, 100%, whatever...but since an employee is
at most 14000, I think 26000 total match from an employer is a pretty good
incentive! Especially since the matched part is tax deferred. And since
even if they contribute less, they can still get a larger profit share to
reach 40K, which is why you reduce the profit sharing, which is taxed at
today's higher tax rate.
I don't want to think about how to adjust fo the change again in July!
(Fiscal year). I think I can just replace the 40000 with 41000, but later on
that!
I do fine with small formulas, but a calculator has always been my tool.
And programs that do the formulas, and you just give it the numbers in the
blanks...but I am really liking Excel. I have started exploring, and am
amazed at what it does. I found statistical functions, which is great!
Those I found I can do easily...
it is similar to how Fathom works. But I will get there.
Thanks so much for all the help. This board has been the greatest tool I
have found since I bought Excel (Office).
Barbara
--
barbarat


" wrote:

"barbarat" wrote:
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?

I doubt that your problem is with understanding "commas
and parentheses [and] what comes first". But if that truly
is your problem, the best thing is to rely on the Help text
for each function. I find the "offline" text better than the
online text because of the See Also link. If you agree, click
on Help, then click on Online Content Settings in the Help
window. Uncheck "Content and links from Microsoft Office
Online". After clicking Okay, you will have to exit and
re-enter Excel to see the effect.

The See Also link might help you with what I suspect is the
real problem: how to know what functions and operators
exist. If you look at the See Also link on the Help text for
the "IF worksheet function", you will discover the AND() and
OR() functions, for example. Careful reading of the syntax
and a general knowledge about how function arguments
and expressions work in programming languages will help
you get it right.

Sorry, but I don't know of any other magic way to learn a
"language" -- which Excel expressions are -- other than
finding a good intro book.

the date thing really confuses me.

Yes, it took me some time to remember DATEDIF() myself.
I did not find any Excel documentation. I found it with a
google search.

if i wanted to use numerical, like 01011984 instead of
mm/dd/yyyy, what would i need to change?

I believe I answered that in an earlier posting in this thread.
Look for it. It isn't pretty. Why do you prefer the "numerical"
format? What problem does that solve for you that the
normal format does not?