Home 
Search 
Today's Posts 
#1




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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Paste Special Multiply Formulas not working in Excel 2007  Excel Worksheet Functions  
How to change the Column Reference when working with multiply work  Excel Discussion (Misc queries)  
formula cell value multiply by percentage  Excel Worksheet Functions  
How do I multiply cell value by percentage?  Excel Worksheet Functions  
percentage not working right  Excel Discussion (Misc queries) 