Calculate Cumulative total as a part of Array

[CA_Punit]

I am stuck with Cumulative total of an array which is a part of Bigger formula.

I have the following

Book11.xlsx
	A	B	C	D	E	F
1	Total Sales	Category	0		a
2	1,99,000.00	1.00	59,700.00	59,700.00	Current	Want this to be
3	3,00,000.00	1.00	49,600.00	-10,100.00	1,01,000.00	101000
4	1,99,000.00	1.00	1,02,866.67	53,266.67	3,00,000.00	4,01,000.00
5	1,99,000.00	1.00	1,42,666.67	39,800.00	37,50,000.00	41,51,000.00
6	1,99,000.00	1.00	1,82,466.67	39,800.00
7	1,99,000.00	1.00	2,22,266.67	39,800.00
8
9
10	Slab	Category 1	Category 2
11	1	0.3	0.4
12	90001	0.2	0.3
13	150001	0.2	0.2
14	900001	0.2	0.2
Sheet1 (3)

Cell Formulas
Range	Formula
C2:C7	C2	=SUM($D$1:D2)
D2:D7	D2	=SUM(IFERROR(LET(a,(($A$12:$A$14-$A$11:$A$13)-IF(($A$12:$A$14-$A$11:$A$13)+IF((C1-($A$12:$A$14-1))>0,0,(C1-($A$12:$A$14-1)))<0,0,($A$12:$A$14-$A$11:$A$13)+IF((C1-($A$12:$A$14-1))>0,0,(C1-($A$12:$A$14-1)))))/CHOOSE(B2,$B$11:$B$13,$C$11:$C$13),b,IFERROR(MATCH(A2,a,1),0)+1,c,IFERROR(MATCH(A2,a,1),0),d,INDEX(a,SEQUENCE(b),1),e,IFERROR(INDEX(a,SEQUENCE(c),1),0),f,INDEX(CHOOSE(B2,$B$11:$B$13,$C$11:$C$13),SEQUENCE(b)),IF(A2>d,d,A2-SUM(e))*f),0))
E3:E5	E3	=(($A$12:$A$14-$A$11:$A$13)-IF(($A$12:$A$14-$A$11:$A$13)+IF((C2-($A$12:$A$14-1))>0,0,(C2-($A$12:$A$14-1)))<0,0,($A$12:$A$14-$A$11:$A$13)+IF((C2-($A$12:$A$14-1))>0,0,(C2-($A$12:$A$14-1)))))/CHOOSE(B3,$B$11:$B$13,$C$11:$C$13)
F4	F4	=E4+F3
F5	F5	=F4+E5
Dynamic array formulas.

Since it is an part of array, I want the cumulative total to be in array.

 

[jasonb75]

How about a description of the task so that we don't have to spend hours trying to decipher your existing formulas?

 

[CA_Punit]

Book11.xlsx
	A	B	C	D	E	F	G	H
2	Say for instance I have a array derieved from a formula and it looks like this
3	Array (for Instance)
4	700						200
5	1000						500
6	1100						600
7
8	now I want to get the cumulative total like
9	700
10	1700
11	2800
12
13
14	Since it is a part of an array formula I cannot refer the array from cells. It will be a part of array
15
Sheet3

Cell Formulas
Range	Formula
A4:A6	A4	=G4:G6+500
A9	A9	=A4
A10	A10	=A5+A9
A11	A11	=A10+A6
Dynamic array formulas.

 

[Fluff]

Is this in relation to this thread. SUMPRODUCT, Tiered Commissions, One for Old Customers, One for New Customers

 

[jasonb75]

That part is obvious, I was referring to the task as a whole.

C2:C7 and D2:D7 appear to be intermediate helper arrays. Taking those away, what is the procedure to get from the values in A2:A7 to the final result?

 

[CA_Punit]

Someway @Fluff but the above is solved. I am fixed as to how to solve the post3

 

[CA_Punit]

@jasonb75 you can refer to SUMPRODUCT, Tiered Commissions, One for Old Customers, One for New Customers

But that is not what i want now. I am fixed with a simple probelm as stated in post 3

 

[jasonb75]

But that is not what i want now.

What you want and what is possible are not always the same thing.

The only way to create a running total in the way that you want to is by nesting subtotal and offset. This will only work with ranges, not with arrays.

 

[Marcelo Branco]

I'm not sure I understand what you want. For an array generated by a formula as simple as the one shown in A4:A6, maybe ...

Select A9:A11 and paste this array formula in the formula bar
=SUBTOTAL(9,OFFSET(G$4:G$6,,,ROW(G$4:G$6)-ROW(G$4)+1))+500*(ROW(G$4:G$6)-ROW(G$4)+1)
Control+Shift+Enter

M.

 

[Eric W]

The only way to create a running total in the way that you want to is by nesting subtotal and offset. This will only work with ranges, not with arrays.

There's another way involving TRANSPOSE and MMULT, but it's ugly. Maybe using LET it might be reasonable. But even so, I think CA_Punit might be going about this the wrong way. Rather than saying, "this is a sub-problem I want to solve", he could present the whole problem. Anything complicated enough to require an in-formula sub formula to have a running total really would be more of a novelty than a usable formula. I think a UDF would make more sense.

Book1
	A	B	C	D	E	F	G	H
1
2	Say for instance I have a array derieved from a formula and it looks like this
3	Array (for Instance)
4	700						200
5	1000						500
6	1100						600
7
8	now I want to get the cumulative total like
9	700						700	700
10	1700						1700	1700
11	2800						2800	2800
12
13
14	Since it is a part of an array formula I cannot refer the array from cells. It will be a part of array
Sheet4

Cell Formulas
Range	Formula
A4:A6	A4	=G4:G6+500
A9:A11	A9	=TRANSPOSE(MMULT(SEQUENCE(,ROWS(A4:A6))^0,IF(ROW(A4:A6)<=(TRANSPOSE(ROW(A4:A6))),A4:A6,0)))
G9:G11	G9	=TRANSPOSE(MMULT(SEQUENCE(,ROWS(G4:G6+500))^0,IF(SEQUENCE(ROWS(G4:G6+500))<=SEQUENCE(,ROWS(G4:G6+500)),A4:A6,0)))
H9:H11	H9	=TRANSPOSE(MMULT(SEQUENCE(,ROWS(A4#))^0,IF(SEQUENCE(ROWS(A4#))<=SEQUENCE(,ROWS(A4#)),A4#,0)))
Dynamic array formulas.