Thanks:  0
Likes:  0

1. I have a column with 20 rows of data (varying dollar amounts). The total of the 20 rows = \$10,066.82. I have two different dollars amounts (\$4,879.25 and \$5,187.57 which are sub-totals of the 20 rows)that I have to identify the # of rows which add up to the \$4,879.25 and the # rows that add up to the \$5,187.57. Can this be done in a formula??

2. To get your subtotals are you using SUM or SUBTOTAL. If you are using SUBTOTAL change the function ref to 2
e.g
SUBTOTAL(9,C3:C5) will generate a subtotal of the cells C3:C5 using the SUM function
whereas
SUBTOTAL(2,C3:C5) will generate a subtotal of the cells C3:C5 using the COUNT function
(If you want to switch between the 2 then reference the function_ref to another cell which you change from 2 to 9

If you are just using SUM then use the corresponding COUNT(C3:C5)

Russell

3.

4. (a) Ensure your data is on A2:A21.
(b) Enter the first Cut of Value in B24
(c) Cut and paste the following formula in B2
=IF(A2+B1>=B\$24,0,A2+B1)
(d) Drag (or copy & paste) B2 till B21.
(e) You should be having a '0' in one of the cells in B2:B21 (say Bn)
(f) The cells A2:An is your first set and rest the second.
(g) Change '>=' in (c) to '=' for exact match

- sam

5. These are the 20 different amounts. Some combination of these amounts will equal \$4,879.25 and the remainder will equal \$5,187.57. It's not as easy as just sub-totaling.
15.55
22.42
58.48
82.74
85.18
172.93
208.89
231.20
244.38
321.78
419.80
499.85
604.24
641.53
869.02
964.20
1,008.79
1,126.70
1,231.87
1,257.27

[ This Message was edited by: M.Young on 2002-03-20 01:20 ]

6. How do you calculate the subtotals at the moment? Is it just a number you are given?

Russell

7. [ This Message was edited by: M.Young on 2002-03-20 05:52 ]

8. Right now, we calculate it out manually. We get the sub-totaled amounts from another office. Our office has the individual amounts (Bills). I have a report that I have to validate how many individual bills make up the sub-totaled amount given to me by the other office. There are usually other indicative entries on each line that help identify the bills within a sub-total. Ex. Type of bill, age of the bill, etc. In this case, all the indicative data is the same. Thus, we have a serious "Brain Teaser" that we have to sort out. I found the solution manually. It took only 8 hours. Not very efficient. I think this could be done with a formula, maybe not within Excell, but maybe within another Excell freindly software. The solution is:
\$1,008.79
172.93
499.85
58.48
321.78
641.53
15.55
964.20
82.74
869.02
244.38
= \$4,879.25

\$1,126.70
1,231.87
1,257.27
208.89
85.18
231.20
419.80
604.24
22.42
= \$5,187.57

[ This Message was edited by: M.Young on 2002-03-20 06:15 ]

9. Just by way of note, with 20 numbers there are 1,048,575 possible combinations.

10. Hi Abdc,

If the value cannot be obtained, that is equivalent to COMBIN(x,0), which is always 1, so change

{ =SUM(COMBIN(20,ROW(1:20))) }
= 1,048,575

to

{ =1+SUM(COMBIN(20,ROW(1:20))) }
=1,048,576

Just an exercise, as your point is very good.

Can anybody help to generalize this by making a reference to a cell rather than hardcoding the 20 in the formula?

This doesn't work (G1 is the reference cell)
=1+SUM(COMBIN(G1,INDIRECT("ROW(1:"&G1&")")))
array entered.

Any help greatly appreciated.

Thanks,
Jay

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•