Remember Me?

#### Menu

#1
September 7th 18, 04:31 PM
 Junior Member First recorded activity by ExcelBanter: Sep 2018 Posts: 2
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

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post el_guapo Excel Worksheet Functions 1 September 25th 08 08:30 PM Andy Excel Discussion (Misc queries) 1 July 18th 07 04:51 PM formula using percentage question Excel Worksheet Functions 3 June 2nd 06 02:18 AM Kelly P. Excel Worksheet Functions 4 October 27th 05 07:27 PM winnieluvpooh Excel Discussion (Misc queries) 3 June 21st 05 11:43 PM

All times are GMT +1. The time now is 12:42 AM.

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

# About Us

"It's about Microsoft Excel"

Copyright © 2017