make cumulative total

[help needed yes please]

Frank here. Can anyone help me work out how to: A1 to A60 are numeric. I want Column 'B' to make a progressive descending total of Column 'A' figures. New at this so yes help would be great, Thanks, F PS: What does it mean when it says here something about 'Tags"?

 

[Aladin Akyurek]

In B1 enter and copy down to B60...

=LARGE(INDEX(SUBTOTAL(9,OFFSET($A$1,0,0,ROW($A$1:$A$60)-ROW($A$1)+1)),0),ROWS($B$1:B1))

 

[help needed yes please]

Hi Aladdin, Frank here; Did as suggested B1 gave answer of 7038 A1 actual 201, A2541, A3 695. Should give running total of B1 201, B2 742, B3 1437 etc. Cant understand where the 7038 came from. I cut and pasted your formula so i cant see how i mucked up. I see from other post your pretty good with this stuff. Sorry if i am lousing it up. Ta F

 

[Marcelo Branco]

Try

B1 copied down
=SUM(A$1:A1)

M.

 

[help needed yes please]

frank again: Noticed in trying to drag formula down it would only drag the number 7038. It took no notice of changed A1 numeric. I don't know if that is significant, just passing it along because that's whats happening. Cheers, f

 

[help needed yes please]

Frank: Thanks Aladin that works a treat, But tell me what does the formula say? Trying to learn here. Thank you again terrific help. I was never in a million years going to sort that out on my lonesome. Thanks, F

 

[Marcelo Branco]

frank again: Noticed in trying to drag formula down it would only drag the number 7038. It took no notice of changed A1 numeric. I don't know if that is significant, just passing it along because that's whats happening. Cheers, f

Are you referring to which formula? Formula in post 2 or in post 4?

M.

 

[Aladin Akyurek]

Frank: Thanks Aladin that works a treat, But tell me what does the formula say? Trying to learn here. Thank you again terrific help. I was never in a million years going to sort that out on my lonesome. Thanks, F

In

=LARGE(INDEX(SUBTOTAL(9,OFFSET($A$1,0,0,ROW($A$1:$A$60)-ROW($A$1)+1)),0),ROWS($B$1:B1))

the ROW bit creates a progressive hight for OFFSET. That means we get a cumulative sum when fed to SUBTOTAL. SUM for A1:A1, SUM for A1:A2, SUM for A1:A3, etc.

LARGE picks out progressive largest of the cumulative sums it's fed with.

Hope this helps.

 

[ask2tsp]

you might try this

Excel Workbook
	A	B
1	71	3168
2	93	3075
3	73	3002
4	13	2989
5	34	2955
6	50	2905
7	83	2822
8	30	2792
9	46	2746
10	22	2724
11	53	2671
12	94	2577
13	34	2543
14	35	2508
15	23	2485
16	85	2400
17	17	2383
18	91	2292
19	88	2204
20	93	2111
21	73	2038
22	6	2032
23	54	1978
24	60	1918
25	95	1823
26	45	1778
27	60	1718
28	37	1681
29	56	1625
30	87	1538
31	56	1482
32	7	1475
33	17	1458
34	47	1411
35	66	1345
36	63	1282
37	77	1205
38	44	1161
39	50	1111
40	22	1089
41	32	1057
42	93	964
43	35	929
44	20	909
45	58	851
46	76	775
47	93	682
48	5	677
49	62	615
50	27	588
51	52	536
52	75	461
53	26	435
54	86	349
55	20	329
56	98	231
57	82	149
58	18	131
59	57	74
60	74	0
Sheet

#VALUE!

</td></tr></table></td></tr></table>

Excel Workbook
	C	D
1	total	3239
total

 

[bosco_yip]

Or..........

1] A1 to A60 are numeric value

2] In B1, copied down :

=IF(A1="","",SUM(OFFSET(A$1,,,COUNT(A1:A$60))))

Regards
Bosco