Cumulative Running SUM with Conditions

[Seba Robles]

Hello all! I'm stuck trying to calculate the running sum of cells I'm referencing. I have a range I'll use as reference ('total value'). In a separate range ('resulting values') I want to calculate the following:

1. Divide the number referenced above in the same column by 4
2. Spill that division across columns so that divided number is shown up to 4 times
3. If there are other values as the formula is dragged across, those should also be divided, aggregated, and spilled across

Let me know if there's any clarifying questions needed, or if there's a better approach to solving this.

Thanks in advance!

 

[6StringJazzer]

The formulas for the first three columns (C:E) are unique, then the formula in the fourth column (F) is copied to the right.

There might be a way to do this with just one formula instead of making the first three unique.

$scratch.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Total Value
2	Group														Total
3	A		$0	$4	$0	$0	$12	$0	$20	$0	$0	$0	$0	$0	$36
4
5	Resulting Values
6
7	A		$0	$1	$1	$1	$4	$3	$8	$8	$5	$5	$0	$0	$36
Allocate by 4

Cell Formulas
Range	Formula
O3,O7	O3	=SUM(C3:N3)
C7	C7	=C3/4
D7	D7	=SUM(C3:D3)/4
E7	E7	=SUM(C3:E3)/4
F7:N7	F7	=SUM(C3:F3)/4

 

[Seba Robles]

Amazing, working perfectly for what I need it. Thank you!

 

[6StringJazzer]

You're welcome, glad it worked for you!

 

[FormR]

Hi, if interested, here's one way to do it with a single formula.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Total Value
2	Group														Total
3	A		0	4	0	0	12	0	20	0	0	0	0	0	36
4
5	Resulting Values
6
7	A		0	1	1	1	4	3	8	8	5	5	0	0	36
Sheet1

Cell Formulas
Range	Formula
O3,O7	O3	=SUM(C3:N3)
C7:N7	C7	=SUM(INDEX($C3:C3,MAX(1,COLUMNS($C3:C3)-3)):C3)/4