OT: MS Excel Question - I doubt you know

[DefyInertia]

Is there a function or way to do the following (aside from writing an excessive amount of IF formulas)?



You've got a large set of numbers (say 300 random numbers in one column, each with their own cell). You know that some combination of numbers contained in the 300 add up to X (could be 2 numbers, could be 299 of the 300). You want to identify the subset that equals X and don't feel like sorting by value and doing the trial by error method...

So? Can it be done using excel?



With my interns at $100/hour and staff at $160+ (internally) this would be very helpful in my quest to keep this project on budget!!!

Thanks...

 

[FZ1inNH]

Is there a function or way to do the following (aside from writing an excessive amount of IF formulas)?



You've got a large set of numbers (say 300 random numbers in one column, each with their own cell). You know that some combination of numbers contained in the 300 add up to X (could be 2 numbers, could be 299 of the 300). You want to identify the subset that equals X and don't feel like sorting by value and doing the trial by error method...

So? Can it be done using excel?



With my interns at $100/hour and staff at $160+ (internally) this would be very helpful in my quest to keep this project on budget!!!

Thanks...

My wife is what I deem the Excel Queen. Accounting and all she works in is Excel. Pehaps if you can give an example of the column of numbers and the "X" you're looking to obtain from the list, I can run it by her and come up with the answer. :thumbup:

 

[Botch]

I can't think of a way it could be done.

However, I'm intrigued about how this "challenge" even came up!

 

[Denver_FZ6]

It sounds as though you need to sum all combinations of numbers in your dataset, then test whether they equal the amount you are looking for.
I don't see a simple formula method to accomplish this. Could probably be done in VB script. Personally I would prefer Perl.

 

[craig007]

is there a only one unique list of numbers that adds up to X? Can you get X by adding 12 number and also get X by adding 156? Do you know if any of the number must be included or excluded? If the list is not unique and you cannot eliminate any numbers, then the only method that I can think of is the brute force method. Maybe sort number from highest to lowest. Eliminate all numbers greater than X. (can use an If statement or one of the database functions). Now add the numbers together until the sum is greater than X. You can do this in the next column (say column B). So if you original numbers are in column A, say B5= B4+A5. So each number in column B is the sum of the previous numbers in column A. Now do a search on column B. If you are lucky you will find X. If you are not lucky find the number in column B that is closest but does not exceed X. Now search for [X-(that column B number)] in column A. If this doesn't work, you can repeat this using the second closest number to X in column B and so on. Excel is really good at repeating a procedure so once you settle on a method you can just keep repeating it.

I don't know of a already defined function that will do this for you. Seems that you would have to write a macro, or some VBasic or look for an add-in function.

 

[reiobard]

I can't even figure out what you are asking, let alone how to answer....


Good luck!

 

[Bren]

Good news: Yes, it can be done with macros. I have that kind of worksheet in use.

Bad news: I don't know the formulas, I only use the file. And it's work related so I can't send you the file as such. :-/

In this file I can place 20 numbers in a list, set the X and the program will point out the numbers that sums up as X. Or if none of the numbers won't make X, it shows wich ones makes the closest sum and what is the difference.

I'll see if I can copy the macro in another file.

 

[GConn]

^^ That's correct. The only way I can think of doing this in excel is using a macro. Macros are programming though and cannot be done with somebody with no knowledge on Visual Basic. Hope that helps.

 

[Howattzer]

Is there a function or way to do the following (aside from writing an excessive amount of IF formulas)?



You've got a large set of numbers (say 300 random numbers in one column, each with their own cell). You know that some combination of numbers contained in the 300 add up to X (could be 2 numbers, could be 299 of the 300). You want to identify the subset that equals X and don't feel like sorting by value and doing the trial by error method...

So? Can it be done using excel?



With my interns at $100/hour and staff at $160+ (internally) this would be very helpful in my quest to keep this project on budget!!!

Thanks...

I have an answer. PM me your address I will send you the sheet.

Cheers,

Howattzer

 

[DefyInertia]

Well, I figured it out. It can be done using the Solver add-in under tools. I have a working sheet that needs some some additional contraints to make it more user friendly. Thanks for the help.

I'm going to PM Howattzer now to see what he came up with.