Working on a formula that will multiply by a percentage b
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 |
All times are GMT +1. The time now is 07:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com