Find 10 items out of 100+ adding up a certain sum

halixol

New Member
Joined
Oct 21, 2006
Messages
3
Hi ,

I have 100+ numbers (3.4, 332.4, 24.12, etc) on my report.
I also have a subtotal (eg. 1234.56) from a separate source.
I know that 10 items out my long report add up this subtotal, but do not know which.
The Question: Can Excel tell me any way which are those 10 numbers that add up the subtotal? :rolleyes:
I know that theoretically there can be many combinations, especially if some numbers duplicate, however in practice this does not happen too often.
Thank you.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Is this homework? I seem to see this type of question every now and then:

"I have n numbers. I also have a given total. Can Excel tell me what combination of m numbers will add up to the given total?"

Surely, if this problem is a dooable one, there must be more than one answer already submitted, and one should be able to find it.

My opinion? For the combination of 100 things taken 10 at a time, Excel's formula: =COMBIN(100,10) gives 17,410,000,000,000. As can be easily imagined, to run a program that tests, on average, half of this number to come up with the various ones whose sum equals the given total may take longer than I have time to wait! So, it may be there is a solution, but, it is not practical.

If I am wrong, I would relish seeing the program that can find the answer(s) in less than, say, 10 minutes. That is, on our normal 3 GHz or so computers, with no more than some 500 MBytes of memory.
 

halixol

New Member
Joined
Oct 21, 2006
Messages
3
This is no homework at all :p , but a problem occuring at least once per week at my workplace. Actually i need to know which are those numbers, and not how many combinations Excel needed to go through ( i believe this your quoted example question about)

Does anyone has a working solution to this problem, and does it really take so much time?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,400
Messages
5,547,735
Members
410,809
Latest member
lilwayne
Top