Can EXCEL list amounts that, when added, total to a specific value? How?

[david.c.mayer]

My task is to generate a list of all "6 value combinations"
that total a collection of specific sums.
Simple Example: All "2 value combinations" that, when added, =13
List: D701709, filled with 1,2,3,4,5,6,7,8,9
Solutions: 4&9, 5&8, 6&7 ( 9&4, 8&5, 7&6 would be considered
duplicates for my task but I can deal with this manually if
they can't be "programmed out" ). This would take care of
one specific sum.
Summary: If I have 10 specific sums for which to perform this, and must
use 10 separate attempts, that would certainly be better
than
what I have now, which is nothing.

Thank you, everyone

Dave

 

[aaron.kempf]

you could cartesian this in a database.

but excel isn't a database
I reccomend losing the training wheels.

-Aaron

 

[Héctor Miguel]

hi, Dave !

My task is to generate a list of all "6 value combinations" that total a collection of specific sums.
Simple Example: All "2 value combinations" that, when added, =13
List: D701709, filled with 1,2,3,4,5,6,7,8,9
Solutions: 4&9, 5&8, 6&7 ( 9&4, 8&5, 7&6 would be considered duplicates for my task
but I can deal with this manually if they can't be "programmed out" ).
This would take care of one specific sum.
Summary: If I have 10 specific sums for which to perform this, and must use 10 separate attempts
that would certainly be better than what I have now, which is nothing.

if you don't mind to use helper columns -?- you might want to give a try to the following approach...

[E701] the specific sum you try to find in combinations [i.e. 13]

[F701] =int(e701/2)
[F702] =mod(e701,2)

[G701] =index($d$701:$d$709,match($f$701-($f$702=0)-(row(a1)-1),$d$701:$d$709,0))
[H701] =index($d$701:$d$709,match($f$701+row(a1),$d$701:$d$709,0))

copy/down 'G701:H701' until you get error values [you could hide'm with FC] or...

add another helper cell [i.e in 'F703'] with:
[F703] =rows(d701:d709)-f701

and modify formulae in 'G701:H701' [range to copy/down] to:

[G701] =if(row(a1)>$f$703,"",index($d$701:$d$709,match($f$701-($f$702=0)-(row(a1)-1),$d$701:$d$709,0)))
[H701] =if(row(a1)>$f$703,"",index($d$701:$d$709,match($f$701+row(a1),$d$701:$d$709,0)))

hth,
hector.

 

[Harlan Grove]

(e-mail address removed) wrote...

you could cartesian this in a database.

And either blow through the machine's physical storage capacity, RAM or
disk, and take a LONG TIME doing so.

but excel isn't a database
I reccomend losing the training wheels.

Indeed, so don't screw around with cartesians. There are procedural
algorithms that would handle this more quickly and using fewer
resources. For example, adapt the code in

http://groups.google.com/group/microsoft.public.excel/msg/7419858047398beb

to check only 6-member subsets.

 

[david.c.mayer]

Hector, I've really given this a "go" to try to expand the "2 value
combination" solution to
more amounts with no success. I sure hate feeling like a dunce.

I expanded the 2 value example to 5, with a target of 20, to compile as many
solutions manually
as I could. This way I could trace my answers into the excel solutions, like
I did with your 2 value
solution. The answers I got were:
1 20 1 1 1 1 1
2 2 2 2 2 2
3 3 3 3 3
4 4 4 4 4
5 5 5 5 5
6 6 6 6
7 7 7
8 8 8
9 9
20 20 20 20 20 20


The only one I could get by changing the results columns from 2 to 5 was the
2,3,4,5,6 answer.
I obviously don't understand how the specific parameters in the lookup_value
portion of the match
function work, or need to work...I apologize but I'm lost.

If you have time could you please model the "5 value combination" to sum 20
for me? Today's
my birthday, and this would be better than anything else I could get.

Thanks...Dave

hi, Dave !

My task is to generate a list of all "6 value combinations" that total a
collection of specific sums.
Simple Example: All "2 value combinations" that, when added, =13
List: D701709, filled with 1,2,3,4,5,6,7,8,9
Solutions: 4&9, 5&8, 6&7 ( 9&4, 8&5, 7&6 would be considered duplicates
for my task
but I can deal with this manually if they can't be
"programmed out" ).
This would take care of one specific sum.
Summary: If I have 10 specific sums for which to perform this, and must
use 10 separate attempts
that would certainly be better than what I have now, which
is nothing.

if you don't mind to use helper columns -?- you might want to give a
try to the following approach...

[E701] the specific sum you try to find in combinations [i.e. 13]

[F701] =int(e701/2)
[F702] =mod(e701,2)

[G701]
=index($d$701:$d$709,match($f$701-($f$702=0)-(row(a1)-1),$d$701:$d$709,0))
[H701] =index($d$701:$d$709,match($f$701+row(a1),$d$701:$d$709,0))

copy/down 'G701:H701' until you get error values [you could hide'm with
FC] or...

add another helper cell [i.e in 'F703'] with:
[F703] =rows(d701:d709)-f701

and modify formulae in 'G701:H701' [range to copy/down] to:

[G701]
=if(row(a1)>$f$703,"",index($d$701:$d$709,match($f$701-($f$702=0)-(row(a1)-1),$d$701:$d$709,0)))
[H701]
=if(row(a1)>$f$703,"",index($d$701:$d$709,match($f$701+row(a1),$d$701:$d$709,0)))

hth,
hector.

 

[david.c.mayer]

Hector, I just read the posted version of my 1st response.

I cut and pasted the data from an EXCEL spreadsheet and it mutated into that
awful listing.

The "5 item, target 20" results I got manually were:
(2,3,4,5,6) (1,2,4,6,7) (1,3,4,5,7) (1,2,3,6,8) (1,2,4,5,8) and
(1,2,3,5,9).
There may, of course, in actuality be more.

Dave

hi, Dave !

My task is to generate a list of all "6 value combinations" that total a
collection of specific sums.
Simple Example: All "2 value combinations" that, when added, =13
List: D701709, filled with 1,2,3,4,5,6,7,8,9
Solutions: 4&9, 5&8, 6&7 ( 9&4, 8&5, 7&6 would be considered duplicates
for my task
but I can deal with this manually if they can't be
"programmed out" ).
This would take care of one specific sum.
Summary: If I have 10 specific sums for which to perform this, and must
use 10 separate attempts
that would certainly be better than what I have now, which
is nothing.

if you don't mind to use helper columns -?- you might want to give a
try to the following approach...

[E701] the specific sum you try to find in combinations [i.e. 13]

[F701] =int(e701/2)
[F702] =mod(e701,2)

[G701]
=index($d$701:$d$709,match($f$701-($f$702=0)-(row(a1)-1),$d$701:$d$709,0))
[H701] =index($d$701:$d$709,match($f$701+row(a1),$d$701:$d$709,0))

copy/down 'G701:H701' until you get error values [you could hide'm with
FC] or...

add another helper cell [i.e in 'F703'] with:
[F703] =rows(d701:d709)-f701

and modify formulae in 'G701:H701' [range to copy/down] to:

[G701]
=if(row(a1)>$f$703,"",index($d$701:$d$709,match($f$701-($f$702=0)-(row(a1)-1),$d$701:$d$709,0)))
[H701]
=if(row(a1)>$f$703,"",index($d$701:$d$709,match($f$701+row(a1),$d$701:$d$709,0)))

hth,
hector.