Formula help

M.Young

New Member
Joined
Mar 18, 2002
Messages
4
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??
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
(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
 
Upvote 0
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
 
Upvote 0
How do you calculate the subtotals at the moment? Is it just a number you are given?

Russell
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top