0

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?

  • 2
    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 at 18:19
  • How your input data looks like? What is your desired output? – Máté Juhász Jul 14 at 19:20
2

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.

enter image description here

  • 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 at 1:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.