Is there an Excel Function to find a combination of numbers
I am not familiar with all of excel's functions and data tools, though I am familiar with excel. My problem is that I have a number on a spreadsheet which is a combination of many different numbers on a list.
For example:
A list contains: 100, 200, 250, 500, and 1000, and the number I need to explain is: 800
The answer would be 500, 200, 100.
Not hard to do in my head for a short list with simple numbers, but I'm dealing with over 1500 currency cells ($xxxx.xx) which make a total (and not all are used, so SUM is useless)--I need to understand which numbers were used to create that total (Which isn't a formula, it's a hard-coded number).
THE QUESTION: Is there a function or VBA that will systematically combine numbers in a given range until it determines which numbers can be added together to make the total?
I just want to know before I dig in and start writing a brute-force algorithm myself.
========
EDIT: The solution I created, attached below
Link to the file I created to do this: https://drive.google.com/file/d/0B8nE67gSOkewWXR2WnRuQTc2MEU/view?usp=sharing
Thank you to everyone who contributed!
Answer
You can use SOLVER in excel to get the result.
You can activate it in ADD-INS and it should show up in DATA tab.
You set up your spreadsheet like this:
In one column You have list of numbers You want to check Next column is all zeroes (0) Third column is First*Second (for example 100 * 0) so in the beginning its zero for all rows
Than You add summary of third column and it should also be zero. Example how this data can look like:
100 0 0
200 0 0
500 0 0
50 0 0
60 0 0
80 0 0
120 0 0
90 0 0
TOTAL 0
Now You run solver form data tab and You get interface that You have to feed parameters:
Goal value is the CELL with the sum of all multiplications You are looking for exact value (type in 800)
By changing cels: select range of zeroes in second column
Add thre additional restriction (add button):
range of zeroes have to be >= than 0
and <= 1
and int so we only have 0 and 1 as possible results
(you have to reselect range every time you add another limitation)
Now press solve and after some time (depending on scale of your data sets ranging from seconds to many minutes) it will change some of the zeroes to 1 indicating which numbers where used to produce Your result.
If there are many possible outcomes it will choose one that he found without indicating that there are more, but running it again may produce different result.
Here is the result I got:
100 1 100
200 0 0
500 1 500
50 0 0
60 0 0
80 1 80
120 1 120
90 0 0
TOTAL 800