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?