# Can Excel figure a combination?

#### Kalas

##### New Member
I'm a CPA and frequently I run into a case where a client has combined numbers out of a series to arrive at a total. For instance, lets say he sent a list of 100 checks and he's combined these 100 checks into 5 different types of expenses. Is there a way I can put in the total for one expense category and have Excel make a suggestion as to what numbers from the list account for the total? I attempted to do this using the Solver add-in, but I don't think this is viable. It's a complex computation, I am sure, so it may outstrip Excel's capabilities.

Thank you,
Troy

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### RalphA

##### Well-known Member
The number of combinarions of 1000 items, taken 5 at a time, is, per Excel's formula:
=COMBIN(1000,50) gives 8.25029E+12, or 8,250,290,000,000
As you can see, on average, you would have to try 1/2 of the above number, to get a result, and, I believe you would have to wait too long to accept this as a practical result. It would make more sense to try to get the actual information from your client. In my opinion, this would be a reasonable request.

Another problem might be that, if two or more of the 1000 items are not unique, you might still have more than one solution to choose from!

#### tusharm

##### MrExcel MVP
To the extent that it can be solved with Solver "optimized" to expect a linear model, see the template
Find a set of amounts that match a target value
http://www.tushar-mehta.com/excel/tips/template-set-match.html

I'm a CPA and frequently I run into a case where a client has combined numbers out of a series to arrive at a total. For instance, lets say he sent a list of 100 checks and he's combined these 100 checks into 5 different types of expenses. Is there a way I can put in the total for one expense category and have Excel make a suggestion as to what numbers from the list account for the total? I attempted to do this using the Solver add-in, but I don't think this is viable. It's a complex computation, I am sure, so it may outstrip Excel's capabilities.

Thank you,
Troy

#### RalphA

##### Well-known Member
Barry:

The site you posted seems perfect for what the original poster needs! Hope he goes there.

Edited 7:59 Central Daylight Time:
The very first time I tried to run the code, I got a "Compile error: Variable not defined", pointing to the variable CHECK. Arrgh! Am I supposed to try to fix this, or has it been fixed already?

Replies
1
Views
53
Replies
0
Views
87
Replies
6
Views
77
Replies
1
Views
5K
Replies
3
Views
81