Finding what multiples to a target number, multiple cells.

Check out xl's solver addin.

http://www.excel-easy.com/data-analysis/solver.html

There is no simple function, formula or built in feature to do this. What your asking sounds simple but is much more complex than people guess. Solver is likely the closest built in option, but that wont give you a combination of variables, only 1 as far as i know.

You are not the first and by far not the last who will ask how to do this. Many people come to this question when trying to balance books, etc.

The problem is, that just because a number of values add up to a sum, doesn't mean they are the values you want unless you are dealing with an extremely limited set of data...in which case its still faster to do manually.

Any automated approach to this will require taking into consideration many factors and assuming a margin of error that may not be acceptable to you. Taking the example you gave, I would be surprised if there wasnt a dozen or more combinations that sum up to the number you have as the total. Which one is right then? The first match found? The one with the least # of "items" (ie: 2x something instead of 5x something)?

The above doesn't even broach what happens when there is no exact match/combination. Thats the other aspect that makes this hard, when do you stop checking combinations? If you exhaust every possible combination that could be thousands, millions, or more guesses which takes time.

So to answer your question, you need to know what qualifies a result (combination of things that sum up to your total) as done, correct, etc?

If you want all combinations, then you can brute force it with a macro.

If you need a single answer, you have to decide the conditions the answer needs to meet to be correct.

hopefully this helps

EDIT: as a matter of fact, after reviewing your example...in the example I do not think there are any combinations that would work...unless you can multiply by decimal numbers (ex: 1.5x). I say this because all the values you gave are even numbers....any sum of all even numbers is always even, but your sum is odd. Im a bit tired, hopefully I didnt put my foot in my mouth but I think I am correct here. If I were, any method of trying to find the combination would exhaust all possible combinations before realizing the same as I did, which could be thousands or millions or more combinations.

I agree with Zer0cool that there probably is not any combination that will work unless you allow for decimals.

Just for fun I set up 3 different models.

My first model used Simplex LP engine and it gave a difference of 55 $ (target value - Objective function).

My second model using the GRT Nonlinear engine gave me a difference of 15 $

And finally the third model using the Evolutionary engine where the difference was 3 $

For the first two models I only needed two constraints. The numbers in range B2:B6 should be integers and all values should be >= 1.

In the third model I did have to set a max limit on the size of the numbers in range B2:B6 i.e. <= 80. If there was no upper limit on these numbers solver added so huge numbers
in the range B2:B6 that when solver started to reduced these numbers there was no significant change in the object function so solver reported it had an optimal solution after 10 seconds.

Alf