Cumulative max with IF AND statements

[401kspreadsheets]

I need help with creating the correct formula for the Employer Matching column (Column G). The employer matching formula is the lesser of 50% up to 6% of Gross Wages OR a maximum of $8,400 (which is 3% of $280,000). Once Gross Wages reach $280,000, the employer match will no longer accrue. Also, the only way the Column G should contain a value is if the Total Contributions in Column E are greater than 0. Column E's cumulative maximum is $19,000. The maximum cumulative amount in Column G should be $8,400.


The logic in Column G should be: If the total in Column B is less than or equal to $280,000, and the applicable cell in Column E is greater than 0, then calculate the employer matching up to a maxiumum total of $8,400.


The following formulas already exist
In cell E3: =IF(B3*(C3+D3)+SUM($E$2:E2)<=19000,B3*(C3+D3),19000-SUM($E$2:E2))
In cell F2: =IF(SUM(C2:D2)>=0.06,0.03,SUM(C2:D2)/2)

	A	B	C	D	E	F	G
1	Pay Date	Gross Wages	401K Pre-Tax % Election	401K Roth % Election	401K Total Contribution ($)	Match Calculation (%)	Employer Matching ($)
2	1/1/2019	$25,000.00	5%	1%	$1,500.00	3.0%	$750.00
3	2/1/2019	$50,000.00	5%	1%	$3,000.00	3.0%	$1,500.00
4	3/1/2019	$25,000.00	5%	1%	$1,500.00	3.0%	$750.00
5	4/1/2019	$25,000.00	4%	1%	$1,250.00	2.5%	$625.00
6	5/1/2019	$25,000.00	5%	1%	$1,500.00	3.0%	$750.00
7	6/1/2019	$25,000.00	5%	1%	$1,500.00	3.0%	$750.00
8	7/1/2019	$50,000.00	5%	1%	$3,000.00	3.0%	$1,500.00
9	8/1/2019	$25,000.00	5%	1%	$1,500.00	3.0%	$750.00
10	9/1/2019	$25,000.00	5%	1%	$1,500.00	3.0%	$750.00
11	10/1/2019	$25,000.00	5%	1%	$1,500.00	3.0%	$275.00
12	11/1/2019	$25,000.00	5%	1%	$1,250.00	3.0%	$0.00
13	12/1/2019	$25,000.00	5%	1%	$0.00	3.0%	$0.00

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[James006]

Hello,

In cell G2, you could test following

=IF(AND(E2>0,SUM($B$2:B2)<280000),F2*B2,8400-SUM($G1:G$2))

Hope this will help

 

[401kspreadsheets]

Hi James006 -

Thanks for the reply. Unfortunately the IF AND statement provided only partially worked. It limited Column G's cumulative total to $8,400 as requested, but the statement you provided also forced the cumulative total to equal $8,400. In other words, hypothetically if Column G, Rows 1-11 only totaled up to $7,000, then Row 12 would be forced to be $1,400, even though B12*F12 may only equal $200.

 

[James006]

Hi,

Do you mean that cumulative total of $8,400 can be something else ... under some circumstances ... ?

If it is the case ... which condition for which new ceiling ...

 

[Joe4]

Put this in cell G2:

=MIN(F2*B2,8400)

Then put this in G3 and copoy down to G13:

=MIN(F3*B3,8400-SUM($G$2:$G2))