Can Excel figure a combination?

Kalas

New Member
Joined
Oct 18, 2006
Messages
1
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
 

Some videos you may like

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
Joined
May 14, 2003
Messages
3,829
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
Joined
May 28, 2002
Messages
11,023
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
Joined
May 14, 2003
Messages
3,829
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,990
Messages
5,545,361
Members
410,679
Latest member
rolandbianco
Top