Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help with Summing
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help with Summing
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMMING COLUMNS IN DIFFERENT WORKSHEETS | Excel Discussion (Misc queries) | |||
Not summing a columns? | Excel Worksheet Functions | |||
Cumulative Summing | Excel Discussion (Misc queries) | |||
Summing Sheets - SUMIF ? | Excel Worksheet Functions | |||
Summing Formula | Excel Worksheet Functions |