# 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.

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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

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

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
0
Views
89
Replies
10
Views
329
Replies
4
Views
744
Replies
0
Views
458
Replies
4
Views
116

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,255
Messages
5,769,061
Members
425,515
Latest member
baltusf

### 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.

### Which adblocker are you using?

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

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