Sum and Max

[Taibot]

Hello all,

Hopefully you can help me.

I'm working on a spreadsheet in which I can have anywhere from 10 to 40,000 rows. These data in these rows are amounts.

I'm trying to mark all amonts which are being sumed to reach a maximum amount.
For example, let's say I have 4 rows with 100, 25, 50 and 75. I need to reach the max amount of 150. I'd like a formula to tell me that row 1 and 3 are the necessary rows to match the amount I need.

Is this even doable?? In order to know if my max amount is reachable I'm already using the following fomula: =MIN(150,SUM(D9:D22001)) but it doesn't tell me which rows/cell the formula is using to reach sur amount.

Hope I was clear enough that someone can help me.

Thank you very much for your help.
Taibot

[Domski]

Hi,

You can do this using the Solver add-in but I'm not sure about with formula.

Dom

[Taibot]

Thank you. I'm afraid I don't know all that much about the solver... How would that work and how would it mark the cells used to reach the amount I'm aiming for?

THank you for your help.

[MarvinP]

Hi Taibot,

Does the sum of all the numbers have to equal, exactly, the goal number? Do you see that this may not be possible with some numbers and goal?

Do you want to use any of the numbers in the range or start from the top and move down to get to the goal?

I guess we need an example.

[Domski]

Using your example I set up the worksheet as attached and then the settings in Solver were:

- Set Target Cell: $C$5
- Equal To Value of: 150
- By Changing Cells: $B$1:$B$4
- Subject to the Constraints: Cell Ref $B$1:$B$4 select 'bin' from the dropdown

Click Solve and away it goes. The values that add up to the desired total will have the 1 in column B.

There may be a better way but I used this once and it seemed to work.

Dom

[Taibot]

Thank you both for looking into my problem.

I've attached the spreadsheet I'm currently working on (well can't seem to be able to attach it so I'll copy and paste a couple of rows to the bottom of the post - please note that I've only copied 100 rows but the actual spreadsheet will have way more). Thank you for your solution Domski but unfortunately, it doesn't seem to want to work as I seem to have too many variables (I can have up to 40,000 different amounts).

Regarding your question, MarvinP, indeed the sum of the amounts may not exactly sum to the requested amount in which case the closest possible would work. Also, I'd rather start from the top and move to the bottom as it's sorted by date (soonest to latest)

Based on the attached, let's assume that I'm trying to figure out which cells would sum (or come close to) $25,000,000. I've tried the solver solution but too many variables (or I screwed it up which is possible).

Again, thank you very much for your help.
Taibot

Doc. Date Due Date Amount
10/7/2010 11/21/2010 2,592.00
3/4/2011 4/17/2011 613.54
3/10/2011 4/24/2011 4,541.92
3/10/2011 4/24/2011 3,502.80
3/10/2011 4/24/2011 2,015.28
3/10/2011 4/24/2011 1,714.40
3/11/2011 4/24/2011 154.40
3/11/2011 4/25/2011 7,411.76
3/11/2011 4/25/2011 6,979.58
3/11/2011 4/25/2011 6,977.52
3/11/2011 4/25/2011 6,009.12
3/11/2011 4/25/2011 5,248.68
3/11/2011 4/25/2011 4,113.96
3/11/2011 4/25/2011 3,385.96
3/11/2011 4/25/2011 1,617.92
3/11/2011 4/25/2011 924.88
3/12/2011 4/26/2011 1,763.84
3/12/2011 4/26/2011 881.92
3/13/2011 4/27/2011 24,278.80
3/13/2011 4/27/2011 11,491.88
3/13/2011 4/27/2011 4,601.17
3/13/2011 4/27/2011 3,260.33
3/13/2011 4/27/2011 2,527.84
3/13/2011 4/27/2011 1,759.96
3/13/2011 4/27/2011 1,611.26
3/14/2011 4/28/2011 21,015.12
3/14/2011 4/28/2011 8,756.30
3/14/2011 4/28/2011 6,986.22
3/14/2011 4/28/2011 5,922.44
3/14/2011 4/28/2011 3,425.98
3/14/2011 4/28/2011 1,267.44
3/15/2011 4/29/2011 21,015.12
3/15/2011 4/29/2011 18,079.20
3/15/2011 4/29/2011 1,055.40
3/15/2011 4/29/2011 1,028.40
3/16/2011 4/30/2011 25,039.90
3/16/2011 4/30/2011 24,305.88
3/16/2011 4/30/2011 23,823.28
3/16/2011 4/30/2011 22,156.80
3/16/2011 4/30/2011 16,740.00
3/16/2011 4/30/2011 11,893.32
3/16/2011 4/30/2011 4,049.36
3/16/2011 4/30/2011 3,815.00
3/16/2011 4/30/2011 3,229.12
3/16/2011 4/30/2011 1,442.70
3/16/2011 4/30/2011 881.92
3/16/2011 4/30/2011 835.36
3/17/2011 5/1/2011 16,174.40
3/17/2011 5/1/2011 7,150.62
3/17/2011 5/1/2011 3,430.80
3/17/2011 5/1/2011 2,412.80
3/17/2011 5/1/2011 2,008.80
3/18/2011 5/2/2011 11,277.92
3/18/2011 5/2/2011 4,167.64
3/18/2011 5/2/2011 3,322.20
3/18/2011 5/2/2011 2,554.76
3/18/2011 5/2/2011 1,595.22
3/18/2011 5/2/2011 1,264.08
3/18/2011 5/2/2011 1,230.72
3/18/2011 5/2/2011 1,208.80
3/18/2011 5/2/2011 1,168.20
3/18/2011 5/2/2011 1,071.64
3/18/2011 5/2/2011 619.20
3/19/2011 5/3/2011 7,933.38
3/19/2011 5/3/2011 5,979.52
3/19/2011 5/3/2011 4,337.05
3/19/2011 5/3/2011 3,727.06
3/19/2011 5/3/2011 3,520.80
3/19/2011 5/3/2011 2,065.92
3/19/2011 5/3/2011 908.80
3/20/2011 5/4/2011 39,698.40
3/20/2011 5/4/2011 38,603.60
3/20/2011 5/4/2011 35,172.86
3/20/2011 5/4/2011 34,020.24
3/20/2011 5/4/2011 32,389.70
3/20/2011 5/4/2011 29,957.98
3/20/2011 5/4/2011 26,383.32
3/20/2011 5/4/2011 24,346.72
3/20/2011 5/4/2011 22,753.50
3/20/2011 5/4/2011 16,946.74
3/20/2011 5/4/2011 14,161.50
3/20/2011 5/4/2011 13,485.82
3/20/2011 5/4/2011 12,556.00
3/20/2011 5/4/2011 11,721.54
3/20/2011 5/4/2011 10,573.92
3/20/2011 5/4/2011 10,218.08
3/20/2011 5/4/2011 8,952.60
3/20/2011 5/4/2011 8,496.90
3/20/2011 5/4/2011 8,159.38
3/20/2011 5/4/2011 8,006.44
3/20/2011 5/4/2011 7,827.24
3/20/2011 5/4/2011 7,666.56

[Alf]

I've tried the solver solution but too many variables

Most likely as the Excel 2003 Solver is not that powerful and you mentioned you could have up to 40 000 different amounts.

Is it not possible to "break up" the data in smaller blocks?

You could try out this link http://www.tushar-mehta.com/excel/te...e_combinations but with that many variables I really don't know if this macro will find a solution.

Another option would be to contact the builders of Solver at http://www.solver.com/index.html and ask if any of their more advanced models is capable of solving a problem like this.

But be aware that these advanced models can be quite expensive.

Alf