Formula help

[M.Young]

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??

[Russell]

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

[sam_d1]

[sam_d1]

(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

Please tell me if this solved your problem
- sam

[M.Young]

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 ]

[Russell]

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

Russell

[M.Young]

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

[M.Young]

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 ]

[Abdc]

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

[Jay Petrulis]

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