Tech Support Guy banner
Status
Not open for further replies.
1 - 11 of 11 Posts

·
Registered
Joined
·
2 Posts
Discussion Starter · #1 ·
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
 

·
Registered
Joined
·
2,255 Posts
Why would you want to do that?
 

·
Registered
Joined
·
2 Posts
Discussion Starter · #4 ·
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
 

·
Registered
Joined
·
2,255 Posts
OK Bobby
Is it p[ossible to upload a version of your spreadsheet without revealing sensitive information.

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?
 

·
Registered
LENOVO t43p AMOUNGST OTHERS
Joined
·
12,970 Posts
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.
 

·
Registered
Joined
·
550 Posts
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......
 

·
Registered
Joined
·
19,896 Posts
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.
 

·
Registered
Joined
·
3 Posts
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. :D

maddog
 

·
Registered
Joined
·
2,255 Posts
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:
 
1 - 11 of 11 Posts
Status
Not open for further replies.
Top