# Sum must equal 10000

I was wondering if there is a way to make a formula to automatically adjust so that the sum of a column doesn't a certain maximum.

For example: I want to contribute \$10,000 to a charity, taking 10% of my income. My income varies per month, so I will probably hit the \$10,000 limit earlier. For simplicity's sake, 10% of my income (in a perfect world):

• Jan - \$2500
• Feb - \$2500
• Mar - \$4000
• Apr - \$3000
• May - who cares.

I'll hit the \$10K in April and I'll go over.

Is there a function either at the final sum cell or within the individual months to not have to manually correct each month?

• I don't understand: This seems like a simple case of 12 empty columns with the sum in the 13th column. Or do I misunderstand the problem? – harrymc Jul 14 '19 at 18:19
• How your input data looks like? What is your desired output? – Máté Juhász Jul 14 '19 at 19:20

## 1 Answer

You can use the `MIN` function to limit a month's charity to no more than what is needed to reach the \$10,000 maximum for the year.

For example, if the income for each month is in the range B2:B13 and charity is to go in C2:C13, you can enter the following formula in cell C2 and copy it to the other cells in C2:C13.

``````=MIN(B2*10%,10000-SUM(C\$1:C1))
``````

The formula will return the lower of:

• 10% of the month's income
• The difference between 10,000 and the sum of the charity amounts from all previous months in the year.

• It was part of a more complex spreadsheet so I needed to use: =min(B2*10%,MAX(0,10000-sum(C\$1:C!), but I think I got it!!! – OGC Jul 15 '19 at 1:18