Do I need nested IF statements?
 

I am trying to calculate a 401(k) match of $0.25 on each $1 the first 4% of
contributions ( and also show impact of alternative formulas.

1. My match formula doesn't work exactly because the contribution
percentages were calculated: the formated contribution rate shows 2% but the
formula gives .25 to some and .50 to others because of the decimals behind
the rate.

2. I would like to create a formula which looks at the contribution rate
and calculates the match as .25*ytd contribution if 1% rate, .50 *contrib if
2%, .75*contrib if 3% and 1.00 on only the first 4% if the contribution is 4%
or higher.

Other than sorting in ascending order, and doing a simple multiplication for
all the 1's, then 2's, etc. is there an IF formula I can use to make this
easier?


Jaramya

Jaramya,

Why not use one of the ROUND functions (ROUND,ROUNDUP,ROUNDDOWN) on the rate
so you only use a whole number?

Example: ROUND(rate,0)

Good luck,

Tom Hayakawa

"Jaramya" wrote:

I am trying to calculate a 401(k) match of $0.25 on each $1 the first 4% of
contributions ( and also show impact of alternative formulas.

1. My match formula doesn't work exactly because the contribution
percentages were calculated: the formated contribution rate shows 2% but the
formula gives .25 to some and .50 to others because of the decimals behind
the rate.

2. I would like to create a formula which looks at the contribution rate
and calculates the match as .25*ytd contribution if 1% rate, .50 *contrib if
2%, .75*contrib if 3% and 1.00 on only the first 4% if the contribution is 4%
or higher.

Other than sorting in ascending order, and doing a simple multiplication for
all the 1's, then 2's, etc. is there an IF formula I can use to make this
easier?


Jaramya