# Cumulative max with IF AND statements

##### New Member
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>

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### James006

##### Well-known Member
Hello,

In cell G2, you could test following

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

Hope this will help

##### New Member
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

##### Well-known Member
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 ...

Last edited:

#### Joe4

##### MrExcel MVP, Junior Admin
Put this in cell G2:
Code:
``=MIN(F2*B2,8400)``
Then put this in G3 and copoy down to G13:
Code:
``=MIN(F3*B3,8400-SUM(\$G\$2:\$G2))``

Replies
7
Views
156
Replies
20
Views
423
Replies
0
Views
379
Replies
6
Views
147
Replies
7
Views
199

1,186,159
Messages
5,956,258
Members
438,243
Latest member
FXA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back