Question for the math/computer programmer/Excel geniuses
 

Simple enough problem. I have a list of 25 numbers. Some, but not all of those numbers, when added up equal a known total. How do I figure out which of the numbers from the list equal my total.

Figured there would be a Java calculator for this, but in searching I don't find any on the web. I saw some advanced Excel/programming discussion, but wasn't able to understand how to translate 100 lines of code to Excel. Anyone know how to pull this off?

I'm not in your target group, but it sounds like an al Gebra problem to me...

I think that will be hard to do with plain Excel, without writing VBA code. I'd start by sorting the numbers in descending order and then solving manually, do it a couple times for different sets of 25 numbers, see if your process can be captured in spreadsheet functions.

but he wants an Al Gore rhythm here.

Well since you brought Al Gore into it:

I'd add up all the numbers, verify the result a couple of times to be absolutely sure it's correct, then report whatever you want anyway (even though it conflicts with the result) and then point to the original data set as "proof". Pretty simple.

So you are trying to find every possible permutation of any combo of these numbers which adds up to the magic number?

That's a Stu Pedasso comment.

Got the numbers and the known total to work for?

Since the numbers vary, x and y?

Yep, the tricky thing here is there could be multiple solutions. Solving this programatically would generally yield the first solution encountered.

I would probably create an array for each number and assign a number to that array for each iteration through the set. I would also store a sum for each iteration. This would allow me to keep track of what I've already added (to avoid duplication) and to see if there are multiple solutions.

The next trick is writing an algorithm that could successfully add each permutation of the numbers in the set. I'd have it start buy just "summing" each individual number, then every unique pair of numbers, then every unique set of three numbers, and so on up to the size of the set.

If you have access to a mainframe, I can whip up a recursive REXX exec that would spit out the combos in a jiffy...

When you say "adding together" do you mean only two numbers can be added together at a time, or can you have more? (ex: x+y only, or x+y, x+y+z, w+x+y+z...etc. ) If it is only two numbers at a time, you can probably write a macro for that using IF statements and < and > tests.

-Z-man.

Ok... here's the "word problem" version:

One of my clients put some business charges on her personal credit card. There were 25 charges in total (personal + business) on the credit card statement for the month. When the bill arrived, she paid a portion of the bill out of her personal checking account (unknown amount), and paid the business charges from the business checking account. So, I have the 25 charges on the credit card statement, and I have the amount paid from the business account.

She has the original statement somewhere, but moved a couple of months ago and it will take time to track down. I'm still surprised there wasn't a Java calculator posted for this.

"when added up equal a known total"

Exactly?
Equal to or greater than?
If so, then by no more than a certain #/%?
Seems it would be the exception rather than the rule that any pair of numbers would equal a known total, no?
Wish I was good at math. :(

So the number of possible sums is combin(25,1) + combin(25,2) + combin(25,3) + ... combin(25,25)?

That is only 33.5 million possible sums. you can do that in your head easy.

Yes... the exact total is the amount she paid from her business checking

Yes, but since we know the target sum, it seems like there is a minimum number of the set required to add up to at least that sum. So, if you add up the set from largest to smallest you can figure out that it will take at least that many members of the set to equal the total. Oh well... I know what i mean! :D

yea it's basically improbable that u'll find the matches. unless the set of 25numbers just happen to only coincidentally have one combination that match up to the total.

tell the client to get her receipts

This is the silly question that computers were made for. Google combination algorithms and wrtie a visual basic program to test all 33.55 millon of them.

The client can request the CC company to send a copy of that statement. It may be easier to then determine what was personal and what was business expense.

-Z-man.

I think that we've spent more time discussing this than it would have taken manually to figure this out...

Hmm - I've thought about it a little more, and have a question which may help clear up your dilema:

Do you know if one of her expenses was for a train ride? If so, was she travelling from Albuquerque, New Mexico to San Jose, California, at an average speed of 103 km/hr, while another tran was travelling from San Jose to Alburquerque at an average speed of 96km/hr? If she left Albuquerque at 3:06pm, and the train leaving from San Jose departed the trian depot at 1:23pm on the same day, at what time will the two trains meet? Bonus question: at what distance from San Jose will the trains meet?

Now, if one of her expenses was for an airline, then I have another question: was the plane on a conveyor belt when it took off?

One more question: did any of the transactions have anything to do with a Black Audi Wagon by any chance?

Yes, this will be on the final exam...

-Z-man.

Apologies for derailing (sic) this thread!