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

Thread Starter
Joined
Jan 9, 2007
Messages
2
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
 
Joined
Jul 25, 2004
Messages
5,458
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

Thread Starter
Joined
Jan 9, 2007
Messages
2
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
 
Joined
Feb 20, 2006
Messages
2,255
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?
 

DaveBurnett

Account Closed
Joined
Nov 11, 2002
Messages
12,970
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.
 
Joined
Oct 10, 2006
Messages
550
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

Joined
Mar 8, 2005
Messages
19,896
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.
 
Joined
Jul 25, 2004
Messages
5,458
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.
 
Joined
Jan 11, 2007
Messages
3
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
 
Joined
Feb 20, 2006
Messages
2,255
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top