Using Excel to calculate scenarios from number set

[pdl13]

I would like to calculate all possible results from a given set of numbers. I am wondering if there is a table that I can place numbers in to to see all possible scenarios calculated out (adding and subtracting). I use Excel to do bank recs, and something like this would be extremely useful for seeing what combination of numbers would help get to a zero balance.

For example, if I a set of numbers placed in cells A1:A10, and need to see all possible products from adding and subtracting the numbers (A2+A3-A1, A5-A1+A4, etc). What can I do?

Please let me know if this is not clear enough.

 

[CraigM]

I don't think that excel has the capability to do this, however I have been playing around with creating an add-in to do it.

However, it starts to get very slow when the list of numbers grows. For a list of n numbers, there are 2^n ways of adding and subtracting them, and 3^n ways if you include the possibility of not including the number in the total (as I think you have indicated you require). Eg for a list of 5 numbers there are 243 ways of combining (including omitting) them, and for 10 numbers this blows out to 59049 (including 1417 ways to sum to 0). Obviously as you get a larger list the possibilities just grow exponentially, and so does the time taken to solve it.

How many numbers would be typically in your list? Quite a few, I'm guessing, if it's a monthly bank reconciliation.

 

[pdl13]

Thanks for the reply. You have it exactly right.

At the point I would use it in my Bank Rec process there really would only be about ten numbers in my list (five would still be very helpful).

I appreciate the help.

 

[pdl13]

Just thought I'd elaborate a little more . . . usually this function would be needed when I've already broken down the variance to a relatively small amount (<$5,000), and would be looking for the value that would get me to zero (and have a list of 10 or less values to play with).

Thanks

 

[just_jon]

The final macro here may be of help -- http://www.mrexcel.com/pc09.shtml

 

[just_jon]

To run --

1] copy everything from Dim INV() As Long thru the last End Funstion statement into the thisworkbook module.
2] load column B with your individual values
3] enter the sum to solve for in F1
4] run the macro Challenge

might take a while. Results of matches from column B will show in column H

 

[Seti]

Here's another way to address the problem. In the example below, cell B1 contains your target error amount and the cells below contain the possible values you can use to get the error. I used Solver to have Excel simulate the possibilities. The formula in C1 is =B1-SUMPRODUCT(B3:B12,C3:C12). Then I used solver with the following set-up:

Set target cell to C1. Equal to value of 0. By changing cells C3:C12. Subject to the following 3 constraints:
1. C3:C12 are integers
2. C3:C12 >=0
3. C3:C12<=1

Make3 sure the add-in solver is loaded of course. Then just run solver. If you examine my example, there are multiple answers to the problem and solver found this one. Hopefully, in your data, there is a unique answer.

Book1
	A	B	C	D
1		1700	0
2
3		1000	0
4		2000	1
5		3000	0
6		4000	0
7		5000	0
8		-100	0
9		-200	0
10		-300	1
11		-400	0
12		-500	0
Sheet1

 

[CraigM]

I've created an add-in which I think does what you want it to. For a list of ten numbers it takes a few seconds, which is bearable, but it starts to stretch out if the list is too much longer. It's much faster if you select the option not to include omissions.
I think it is a better solution than the other two suggestions. The code just_jon linked to doesn't allow the possibility to subtract each number in the list, only to add or ignore it. The solver solution works, but It does only give one possible solution. With the example Seti used, my add-in found 123 solutions to the problem. Of course there wouldn't usually be so many possible solutions when using amounts that cannot be made up by combining other amounts (eg in Seti's example 300=300, or 100+200, or 500-200,or 400-100 etc). But it is quite common to have more than one possible solution, so you'll want to know all of them.
Anyway, I'll email it to you if you like. If you're not familiar with using add-ins I'll type some instructions for you if you like. Send me a private message if you don't want to post your email address.

 

[pdl13]

Thanks everyone for all the help!

CraigM - You are right about there usually being more than one solution. It would be spectacular to be able to see all possible solutions. I'll send my e-mail address in a PM.

 

[CraigM]

Ready when you are.