Could this be done? unwinding an aggregate total

[Xceller]

A
Row1: 46.77
Row2: 673.88
Row3: 319.09
Row4: 929.54
Row5: 738.66
Row6: 150.84
Row7: 688.19
Row8: 829.88
Row9: 368.05
Row10: 118.19
Total 4,863.09

I have the above dataset that contains all values from A1 to A10

Problem:

If I am given an aggregate total from the various values of the dataset, it could be any unknown combination from the list (Example: (A1 + A5 + A10 = 903.62) In other words, X(A1) + Y(A5) + Z(A10) , given X, Y and Z are unknown). Is it possible to solve X, Y and Z?

End result:

I am hoping to activate a Macro with an Input Box1 " Enter aggregate Value" with or without another Input Box2 " Number of combinations" in this example the value would be 3.
Then, Output Box " The nearest combination is A1 +A5 +A10 or 46.77 + 738.66 + 118.19

Appreciate your help in advance.

 

[shg]

Are you comfortable downloading and installing an add-in?

 

[Xceller]

I have done it once. I can probably figure it out if you could kindly provide a little guidance. Thanks

 

[shg]

The add-in is at http://www.box.net/shared/sbscyk5dzb

Open the VBE, and in the Immediate windows, enter

? Application.LibraryPath

Put the add-in in that directory. Then in Excel, Tools > Add-Ins, browse to the folder, and select CombinationSums. Then do Ctrl+Shift+S to bring up the form, and follow your nose from there.

There are some UI issues, so it's not fully cooked. Output for your example data:

       A- --B--- C --------------D---------------
   1    1  46.77    1 5 10                       
   2    2 673.88   1 of 0 solutions requested    
   3    3 319.09   Max of 0 items totaling 903.62
   4    4 929.54   Combinations evaluated: 1,021 
   5    5 738.66   Elapsed time: 0:00:01.0       
   6    6 150.84                                 
   7    7 688.19                                 
   8    8 829.88                                 
   9    9 368.05                                 
  10   10 118.19

 

[Xceller]

?Application.LibraryPath
C:\Program Files\Microsoft Office\Office12\LIBRARY

I am having trouble getting / locating the Add-in file
I am using Excel 2007. I downloaded it, then went to Excel options but I can't find the file.

 

[shg]

When you download the file from box, save it to that location.

Then Excel Options > Add-Ins, click the Go button at the bottom of the page, browse to the directory.

 

[Xceller]

It works now. Thank you so much for this. It's so cool, but it's way beyond me.

There are 2 boxes on the Input Form I am not sure what they really are:

# Sol'ns >>>Is this the number of combination?
MaxNums >>>Maximum combinations?

 

[shg]

# Sol'ns > Max number of solutions to find (0 finds all)

MaxNums > Max numbers to use in each combination (0 finds any number of numbers adding to the total)

 

[Xceller]

Got it. Thanks again.