Finding right combination of numbers

[shocktrop]

Hi there,

Is there a way to determine the right combination of numbers that would sum up to a certain number either through vba or a built in excel function?

for example:

a1 1
b1 2
c1 3

we want to pick the numbers that sum up to 3. The function would pick cell a1 and b1 or high light them.

Your help would be appreciated

[MarvinP]

Hi shocktrop and welcome to the forum,

I think we need a bigger and more real example. Why wouldn't C1 alone be the correct answer? It would equal to three all by itself.

What would expect if no combination of the possible numbers would sum to the desired answer? Would you claim it wasn't possible or simply give the closest answer? What if different combinations of numbers added up the the desired answer?

A better or real example might be needed to get closer to answering the bigger question.

[Armando Montes]

Maybe this:
http://www.tushar-mehta.com/excel/te...e_combinations

[MarvinP]

Hi Armondo,

I've seen these "find the numbers" problems before. They normally tell how many numbers you get to select to add to some target value. Because this OP (original poster) didn't specify, it could be any number of numbers, which makes the problem MUCH harder.

My approach is to let the computer guess, using random numbers, and see if the guess is correct. For us humans this is time consuming but computers have all night to work on the problem.

We had one like this a few months ago where an accountant was short some amount. He thought it was three of the 100 transactions that were missed. I took three of the numbers at random and added them to see if it was his missing total. I found two or three possibilities from the 100 numbers by letting the computer guess.

Look at my attached for my approach. It takes about a second to find the answer in my problem.