Conditional formula

[Per Claussen]

Case:
Column C has formula: =B*Amount
Column D has formula: =C*percentOfAmount

My problem:
I need the D formula to check if sum of the D cells (above and actual cell) will pass the maxOfAmount - in this case 5% of 10 000 000.
If so, the actual cell should contain the difference between the maxOfAmount and the sum of the above D cells. If difference is negative, the value should be zero.
We can also express it like this: the sum of column D should be exactly "maxOfAmount"

How should this formula look like?

 

[Luke M]

Change formula in D4 to be:
=MIN(Amount*MaxOfAmount-SUM(D$3:D3), C4*percentOfAmount)
Copy down as needed. Now the sum of col D will not exceed Amount*maxOfAmount. (Note that with your given example, current sum is < max amount, so no different is seen. If you put a number <=41% in B8, that's when you'll see the limit kick in.) Is this what you wanted?

 

[Per Claussen]

Perfect Luke, this was exactly what I needed - thanks a lot