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)

[TABLE="class: grid, width: 933"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Pay Date[/TD]
[TD="align: center"]Gross Wages[/TD]
[TD="align: center"]401K Pre-Tax % Election[/TD]
[TD="align: center"]401K Roth % Election[/TD]
[TD="align: center"]401K Total Contribution ($)[/TD]
[TD="align: center"]Match Calculation (%)[/TD]
[TD="align: center"]Employer Matching ($)[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]$25,000.00[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]1%[/TD]
[TD="align: center"]$1,500.00[/TD]
[TD="align: center"]3.0%[/TD]
[TD="align: center"]$750.00[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]2/1/2019[/TD]
[TD="align: center"]$50,000.00[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]1%[/TD]
[TD="align: center"]$3,000.00[/TD]
[TD="align: center"]3.0%[/TD]
[TD="align: center"]$1,500.00[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]3/1/2019[/TD]
[TD="align: center"]$25,000.00[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]1%[/TD]
[TD="align: center"]$1,500.00[/TD]
[TD="align: center"]3.0%[/TD]
[TD="align: center"]$750.00[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]4/1/2019[/TD]
[TD="align: center"]$25,000.00[/TD]
[TD="align: center"]4%[/TD]
[TD="align: center"]1%[/TD]
[TD="align: center"]$1,250.00[/TD]
[TD="align: center"]2.5%[/TD]
[TD="align: center"]$625.00[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]5/1/2019[/TD]
[TD="align: center"]$25,000.00[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]1%[/TD]
[TD="align: center"]$1,500.00[/TD]
[TD="align: center"]3.0%[/TD]
[TD="align: center"]$750.00[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]6/1/2019[/TD]
[TD="align: center"]$25,000.00[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]1%[/TD]
[TD="align: center"]$1,500.00[/TD]
[TD="align: center"]3.0%[/TD]
[TD="align: center"]$750.00[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]7/1/2019[/TD]
[TD="align: center"]$50,000.00[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]1%[/TD]
[TD="align: center"]$3,000.00[/TD]
[TD="align: center"]3.0%[/TD]
[TD="align: center"]$1,500.00[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]8/1/2019[/TD]
[TD="align: center"]$25,000.00[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]1%[/TD]
[TD="align: center"]$1,500.00[/TD]
[TD="align: center"]3.0%[/TD]
[TD="align: center"]$750.00[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]9/1/2019[/TD]
[TD="align: center"]$25,000.00[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]1%[/TD]
[TD="align: center"]$1,500.00[/TD]
[TD="align: center"]3.0%[/TD]
[TD="align: center"]$750.00[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]10/1/2019[/TD]
[TD="align: center"]$25,000.00[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]1%[/TD]
[TD="align: center"]$1,500.00[/TD]
[TD="align: center"]3.0%[/TD]
[TD="align: center"]$275.00[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]11/1/2019[/TD]
[TD="align: center"]$25,000.00[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]1%[/TD]
[TD="align: center"]$1,250.00[/TD]
[TD="align: center"]3.0%[/TD]
[TD="align: center"]$0.00[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]12/1/2019[/TD]
[TD="align: center"]$25,000.00[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]1%[/TD]
[TD="align: center"]$0.00[/TD]
[TD="align: center"]3.0%[/TD]
[TD="align: center"]$0.00[/TD]
[/TR]
</tbody>[/TABLE]

 

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