I have a large list of data, and certain subtotals.
Is there any way to have excel calculate which combination(s) of the data sum to form a subtotal? This is really important, and if anyone could help I would appreciate it!
I have a large list of data, and certain subtotals.
Is there any way to have excel calculate which combination(s) of the data sum to form a subtotal? This is really important, and if anyone could help I would appreciate it!
Use walking ones to generate the combinations:
For example, you have five items: 1,3,5,7,11 and you want to know which
sub-set will add up to 24.
In A1 put:
=ROW()
in B1 put:
=DEC2BIN(A1,5)
and copy down thru row 31. In C1 thru G1 put:
=(LEFT(B1,1))*1
=(MID(B1,2,1))*1
=(MID(B1,3,1))*1
=(MID(B1,4,1))*1
=(RIGHT(B1))*1 and also copy down thru row 31
In H1 thru L1 put your data:
1, 3, 5, 7, 11 and copy down
In K1 put:
=SUMPRODUCT(C1:G1,H1:L1) and copy down
Look for the 24 and get your sub-set.
--
Gary''s Student
"mrharlow" wrote:
>
> I have a large list of data, and certain subtotals.
>
> Is there any way to have excel calculate which combination(s) of the
> data sum to form a subtotal? This is really important, and if anyone
> could help I would appreciate it!
>
>
> --
> mrharlow
> ------------------------------------------------------------------------
> mrharlow's Profile: http://www.excelforum.com/member.php...o&userid=31423
> View this thread: http://www.excelforum.com/showthread...hreadid=511256
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks