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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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,029
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?
 

Forum statistics

Threads
1,141,930
Messages
5,709,400
Members
421,635
Latest member
mehdi hannechi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top