Cumulative max with IF AND statements

401kspreadsheets

New Member
Joined
Mar 28, 2019
Messages
4
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)

ABCDEFG
1Pay DateGross Wages401K Pre-Tax % Election401K Roth % Election401K Total Contribution ($)Match Calculation (%)Employer Matching ($)
21/1/2019$25,000.005%1%$1,500.003.0%$750.00
32/1/2019$50,000.005%1%$3,000.003.0%$1,500.00
43/1/2019$25,000.005%1%$1,500.003.0%$750.00
54/1/2019$25,000.004%1%$1,250.002.5%$625.00
65/1/2019$25,000.005%1%$1,500.003.0%$750.00
76/1/2019$25,000.005%1%$1,500.003.0%$750.00
87/1/2019$50,000.005%1%$3,000.003.0%$1,500.00
98/1/2019$25,000.005%1%$1,500.003.0%$750.00
109/1/2019$25,000.005%1%$1,500.003.0%$750.00
1110/1/2019$25,000.005%1%$1,500.003.0%$275.00
1211/1/2019$25,000.005%1%$1,250.003.0%$0.00
1312/1/2019$25,000.005%1%$0.003.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
Joined
Apr 4, 2009
Messages
3,680
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
 

401kspreadsheets

New Member
Joined
Mar 28, 2019
Messages
4
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
Joined
Apr 4, 2009
Messages
3,680
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
Joined
Aug 1, 2002
Messages
60,892
Office Version
  1. 365
Platform
  1. Windows
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))
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

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
Top