Thread: Min and max but not more than a total of these.

I'm very new to excel, only did basic stuff in high school and that was almost 10 years ago. What I'm searching for is a way to have cells B12:B15 display a value that is not greater than 1,000 and not less than 0. The combined total of cells B12:B15 can not exceed 2,000. They will each have their own different number. but yeah, I've been at this for about 2 hours now searching, gave up and decided to ask lol. Also, Each cell in the array B12:B15 will have another calculation like that needs to be equal to this.

B12 is =SUM(C12*.6653) This can not exceed 1000
B13 is =SUM(C13*.5882) This can not exceed 1000
B14 is =SUM(C14*.7272) This can not exceed 1000
B15 is =SUM(C15*.6150) This can not exceed 1000

As stated above, the combined total of all of these can not exceed 2000 and i will basically need to get as close to it as possible staying within the rules.
Anyways, if there's a way to find that, that would be awesome. Also, sorry if this has been asked before XD

Hi

What is in C12:C15? User input?

rylo

It would be sums from other calculations.

C12 = 7000
C13 = 2800
C14 = 3588
C15 = 17087

But now that i look at that i find that i have another problem to tweak out.

C3 has a value that no matter what will add 11% to C6.
C4 has a value that no matter what will add 0% to any other cells.
C5 has a value that no matter what will add 20% to C6.
C6 has a value that no matter what will add 21% to C5.

I have the circle thingy solved, but as i said i just discovered i also need to find a way to eliminate the added %'s from the C12, C13, C14 and C15 cells. So say i have 0 in B5, that will say 35.8841 in C5. That cell is directly related to cell C14. In this case as it's suppose to be 0 in B5, I need to find B14 and B15 as 0. Confusing isn't it? lol. Thank you for the quick reply btw

Hi

OK, given all the related cells, how about you set up an example workbook for review.

rylo