make cumulative total

help needed yes please

Board Regular
Joined
Apr 19, 2018
Messages
63
Office Version
  1. 2010
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"?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
you might try this
Excel Workbook
AB
1713168
2933075
3733002
4132989
5342955
6502905
7832822
8302792
9462746
10222724
11532671
12942577
13342543
14352508
15232485
16852400
17172383
18912292
19882204
20932111
21732038
2262032
23541978
24601918
25951823
26451778
27601718
28371681
29561625
30871538
31561482
3271475
33171458
34471411
35661345
36631282
37771205
38441161
39501111
40221089
41321057
4293964
4335929
4420909
4558851
4676775
4793682
485677
4962615
5027588
5152536
5275461
5326435
5486349
5520329
5698231
5782149
5818131
595774
60740
Sheet
#VALUE!
</td></tr></table></td></tr></table>
Excel Workbook
CD
1total3239
total
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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