Formula Needed to Solve from one Group of cells then subtract to limit....

[excelteam777]

This is a complicated one, please help.

Ok, Using the following cells here is what I want to accomplish.

A1 - A10 These cells will have a value, EXP: 2000
B1 - B10 These cells will have a value based on multiplying a percentage from column A. EXP: A1 = 2000 , B1 = A1*10% = 200 NOTE: B cells represent the deductive amount
C1 This cell will have a fixed number EXP: 900 which will represent the overall maximum amount that the B cells will be limited to.

Based on the example above, I would like to have the B cells range stop deducting from the A cells range once the C1 fixed total is reached.

So if I entered a value of 2000 in each of the A cells (A1 through A10) B1 through B4 would show the value of 200 which at this point would total 800 and then B5 would show only 100 because the overall limit total in C1 is 900. B6 through B10 would show 0 due to the limit set in C1.

I hope I was able to explain this easily.

[oeldere]

show it in your excel file, without confidentional information.

please also add the desired (expected) result.

then we don't have to rebuilt the sheet.

[Ace_XL]

Like this..??

[excelteam777]

Yes, thank you soooo much

[excelteam777]

Actually one problem.... B1 does not have the formula so if A1 value is say 10,000 , then B1 shows 1,000 instead of maxing out at 900. Can you fix?

[Ace_XL]

Change in B1 only..

=MIN(A1*0.1,C1)

Rest formulas remain same

[excelteam777]

thank you I appreciate it