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 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
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
174
Replies
14
Views
473
Replies
2
Views
671
Replies
0
Views
355
Replies
20
Views
287

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.

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

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