I'm trying to create a formula that multiples a value from another cell by a percentage but the final result can't exceed a certain amount if the sum of the column exceeds a value.

For example:
$5,160.00 $309.60 $2,064.00
$4,020.00 $241.20 $1,608.00
$5,580.00 $334.80 $2,232.00
$6,000.00 $360.00 $2,400.00
$6,750.00 $405.00 $2,700.00
$7,350.00 $441.00 $2,940.00
$7,920.00 $475.20 $3,168.00
$7,350.00 $441.00 $2,940.00
$8,340.00 $- $100.00
$58,470.00 $3,007.80 $20,152.00
$3,000.00 $18,000.00


The amounts in the Column A are values entered
Column B & C are calculated by a percentage, ROW 10 are the Sum of each column. Row 11 are the max or not to exceed values for that Column. Column B has a MIN of $0 and Column C will be $100 even after the MAX of $18000 is reached.

What I want the formula to do is not perform the calculation once the sum of each column exceeds the MAX value or return a minimum value. Also I need to have the ROW that causes the value to exceed the MAX to return a result that will then meet the max.

Cell B7 makes the SUM $2566.80 so Cell B8 should return a value of $433.20 and Cell C7 makes the SUM $17112.00 so Cell C8 should return a value of $888.00. The next row should return a value of $0 for Row B and $100 for Row C.

I'm really having a had time figuring this out.

Any help will be appreciated.

Thanks

Tim