formula for a pension

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