excel function - 401K contribution calculation

[sidel80]

Hiya,

I was wondering if anyone can help with this excel function.

I have two columns – A & B. A=employee wage, B=employee contribution. I’m trying to calculate how much 401K the company would match assuming:

- First 3% 100% match
- Next 2% 50% match
- Any employee wage over $200K is limited to just $200K matching.

I was hoping to do this all in one function. Any help? Thanks!

 

[PA HS Teacher]

I'm sure it can be done more efficiently with a lookup function, but how about the following for now:

=MIN(B1,0.03)*IF(A1>=200000,200000,A1)+
  MIN(B1-0.03,0.02)*(B1-0.03>0)* 0.5*IF(A1>200000,200000,A1)

Book1
	A	B	C	D
1	$250,000.00	5.0%	$8,000.00
2	$200,000.00	5.0%	$8,000.00
3	$100,000.00	2.0%	$2,000.00
4	$100,000.00	3.0%	$3,000.00
5	$100,000.00	4.0%	$3,500.00
6	$100,000.00	5.0%	$4,000.00
7	$100,000.00	9.0%	$4,000.00
Sheet1

 

[rrdonutz]

Another formula that returns the same results (without a lookup):

=MIN(A1,200000)*(MIN(B1,3%)+MEDIAN(B1-3%,0,2%)/2)