Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 7th 18, 04:31 PM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2018
Posts: 2
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Paste Special Multiply Formulas not working in Excel 2007 el_guapo Excel Worksheet Functions 1 September 25th 08 08:30 PM
How to change the Column Reference when working with multiply work Andy Excel Discussion (Misc queries) 1 July 18th 07 04:51 PM
formula cell value multiply by percentage formula using percentage question Excel Worksheet Functions 3 June 2nd 06 02:18 AM
How do I multiply cell value by percentage? Kelly P. Excel Worksheet Functions 4 October 27th 05 07:27 PM
percentage not working right winnieluvpooh Excel Discussion (Misc queries) 3 June 21st 05 11:43 PM


All times are GMT +1. The time now is 11:10 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017