make cumulative total

help needed yes please

Board Regular
Joined
Apr 19, 2018
Messages
54
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"?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,154
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

Board Regular
Joined
Apr 19, 2018
Messages
54
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
 

help needed yes please

Board Regular
Joined
Apr 19, 2018
Messages
54
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

Board Regular
Joined
Apr 19, 2018
Messages
54
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,154
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

Active Member
Joined
Feb 18, 2015
Messages
488
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
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
2019
Platform
Windows
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
 

Forum statistics

Threads
1,089,557
Messages
5,408,947
Members
403,245
Latest member
Nanda Kishore

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top