Excel (or other program) to find specific totals out of a list?

March 28, 2017 3:54 PM

I have a list of charges and need to figure out which went through based on the total. Can Excel do this? Could a different program? Example below.

For example:

List of charges
$22
$42
$81
$23

Total needed: $64

The program or site would need to run through all possible combinations of those charges and spit out the two (or more) items that add up to the prefered total.

I've done a lot of googling, but the only way I found to do this using Excel is using some extension called "Solver" which my work computer doesn't seem to have. Even if IT responded to my email, I'm not sure if they'd let me download an extension.

posted by jenlovesponies to Computers & Internet (3 answers total)

Best answer: Solver seems be part my my work computer's installation of Excel. Can you try searching your computer for SOLVER.XLAM? Mine is at this path:

C:\Program Files (x86)\Microsoft Office\root\Office16\Office16\Library\SOLVER\SOLVER.XLAM

If you have this file, then you just have to add it into Excel - My computer also did not have Solver loaded in by default. I did the following to load it in to my version of Excel:

1) Data tab > right-click the ribbon > Customize the Ribbon...
2) In Excel Options dialog, select Add-ins. In the list of Add-ins, Solver Add-in should be listed. Select it then click the Go button.
3) Add-ins dialog pops up. Check on Solver Add-in, click OK button. Solver should then be added to Analyze group in your Data tab.

If you feel like going down a programming route, this Stack Overflow question also sounds like what you're going after.
posted by Seboshin at 4:34 PM on March 28, 2017

The solution is not necessarily unique. For example, if you had 10 charges of $8, you wouldn't know which subset of 8 you wanted. Or just add a charge of $41 to your list, and you would have 2 possible solutions.
posted by SemiSalt at 4:48 PM on March 28, 2017

This is a combination which is different than a permutation becasue the order does not matter. Here is a page that explains and has some VBA code to give you a list of all the combinations. You'll need one array for each number of options you can pick: a list of 5 means you need one array for 1, one for 2, one for 3, one for 4 and one for 5. Once you have a list of all the possible combinations, you need to calculate the total of each combination and filter for the ones that match.
posted by soelo at 8:13 AM on March 29, 2017