How to stop sum of cells after a number is reached?

Question

Level 1

9 points

How to stop sum of cells after a number is reached?

I am looking for a way to calculate EI (insurance) deductions and have them stop after it has reached a maximum number, I would like the last cell to calculate the remaining difference. They are based on a percent of total pay (base + bonus) but stop once the max contribution limit is reached.

in the example below, I would like row 6 to display the percent (c2) of sum of base pay and bonus pay but once it reaches the cell limit amount in F1 stop only after displaying the last difference in the last cell.

below is what the final numbers should look like but I would like it to automatically update when I add bonus pay or adjust base.

Any help is greatly appreciated!!!

Posted on Feb 16, 2021 8:15 AM

Reply

Answer 1

Best reply

Barry

Level 9

59,867 points

Feb 16, 2021 12:14 PM in response to bnajmeddine

This will do what you want.

The formula shown is entered in the selected cell (B5) and filled right to H5 (or further, as needed.

MIN(SUM(B3:B4)×$C$1,$F$1−SUM($A5:A5))

The first SUM calculates the amount due for the earnings shown in 'this coumn' SUM(B3:B4)×$C$1

The second SUM calculates the amount paid up to, but not including 'this column, then subtracts that amount from the EI max to determine the amount still to be paid. $F$1−SUM($A5:A5)

MIN then chooses the smaller of the two calculated values.

Row 6 is included only to demonstrate the total EI deduction to and including 'this column', and is not necessary to the solution.

B6: SUM($A5:B5

Regards,

Barry

Reply

Answer 2

bnajmeddine Author

Level 1

9 points

Feb 17, 2021 3:22 PM in response to Barry

This worked! Thank you very much for your help.

Reply

Answer 3

Barry

Level 9

59,867 points

Feb 17, 2021 6:27 PM in response to bnajmeddine

You are welcome. Thanks for the green checkmark.

Regards,

Barry

Reply

How to stop sum of cells after a number is reached?

Similar questions