# Difficult Excel question

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

#### BobbyJones

Does anyone know a way or if it is even possible to have a column of numbers and find X amount of those numbers that sum to a given total.
For example:
Say you have a list of 2, 4, 5, 9, 10
Is it possible for excel to find which of those numbers sums to 18
Is there a way to make excel find 4, 5, and 9 (totaling 18) but exclude summing the 2 and 10 cause they dont help add up to the 18?

Please let me know if you have any ideas.
or you need me to explain it better.

Thanks

-bobby

#### The Villan

Why would you want to do that?

#### Zack Barresse

You're bordering on the limits of standard Excel. Do a google search for "Excel Solver". This has been brought up many times in the past.

#### BobbyJones

Villan: It is something that would really help me out with work (accounting). Find out which numbers a tie out is comprised of.

Firefytr: I found one other post on this web site but there were not any successful solutions.
I was also unsuccessful with google searches and Solver which I am familiar with.

any other thoughts or ideas?

-bobby

#### The Villan

OK Bobby

Then we can get stuck into it.

I have an accounting background so may well be able to lead you in the right direction. Are you grouping parts of balance sheet or P&L or soemthing like that?

#### DaveBurnett

##### Account Closed
I find that, when I have that sort of problem, the answer lies more often in mis-entered or missing values. What you are asking for will not help in those situations.

#### idowindows

I think that even if it is feasable (and something tells me that it is), another issue would arise when several combinations of the listed numbers equal the target figure......

#### OBP

It is possible with a lot of code and as idowindows says it would have to allow for more than one set meeting the requirements.

#### Zack Barresse

Goal Seek is another option. Goal Seek and Solver is designed for these situations. The only difference being Goal Seek takes a single variable, while Solver can take many.

BobbyJones,

Such an algorithm would make use of Combinatorics (math). For x numbers the number
of choices (how many ways) grows as a factor of x! as x grows. This number gets big
fast. When not all number make the sum but only a subset, the value grows fast yet a
somewhat slower rate of x! k! / (k-x)! x!.

FYI the value x! (Factorial) is the product of each number from 1 to x.

So a brute force algorithm would be to write a VBA macro that counts down from x to 1
sequentially, indexing an array of your list of numbers (choices). Then by adding
incrementally the choices of the contents of the array to meet the sum. This method will
take longer than time itself for any values of x greater than say 10.

One suggestion would be how to throw out choices by comparing each choice to be less
than the sum. You would skip the choices greater. This would allow for say 6 out 30
numbers to be doable and not lock up your machine with endless processing.

As for the utility of doing such a thing, I can not comment on accounting. I would say
though this method does work for code breaking - though the numbers are usually
prime numbers (numbers only divisible by 1 and itself).

Hope this helps.

#### The Villan

There is a function called COMBIN. If you can't find this function, you may need to load the Analysis Toolpack (Tools, Addins, Analysis Toolpack).

COMBIN

Returns the number of combinations for a given number of items. Use COMBIN to determine the total possible number of groups for a given number of items.

Syntax

COMBIN(number,number_chosen)

Number is the number of items.

Number chosen is the number of items in each combination.

Remarks

Numeric arguments are truncated to integers.
If either argument is nonnumeric, COMBIN returns the #VALUE! error value.
If number < 0, number_chosen < 0, or number < number_chosen, COMBIN returns the #NUM! error value.
A combination is any set or subset of items, regardless of their internal order. Combinations are distinct from permutations, for which the internal order is significant.
The number of combinations is as follows, where number = n and number_chosen = k:

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

As Seen On