Cumulative Moving Average Help

[nparrillo]

Hi All,

I am trying to get help with a 3 month cumulative moving average. In my attached example I will have 2 columns of data, Water Producted & Water Disposed Cost. In a third column I am trying to calculate the cumulative moving average looking backward.

For example: Month 3: (SUM Month 3,2,1 Water Produced) / (SUM Month 3,2,1 Water Disposed = $1.15 as shown in the example. As you move to the next month it would shift accordingly. Month 1 would just be itself, Month 2 would be Month 1 & 2. and then so on.

I have previously used and equation similar to =SUM(TAKE(B$2:B2,,(-1*3)))/SUM(TAKE(A$2:A2,,(-1*3))) where it had worked fine but I cannot get it to work. See attached example.

Thank you!

 

[StephenCrump]

It looks like you need H2: =SUM(TAKE(B$2:B2,-3))/SUM(TAKE(C$2:C2,-3))

 

[nparrillo]

It looks like you need H2: =SUM(TAKE(B$2:B2,-3))/SUM(TAKE(C$2:C2,-3))

Column H is supposed to show what the values should be if the formula worked. I have manually done a simple formula for each of the cells in H to show.

The actual formula I listed, does not seem to move properly.

 

[StephenCrump]

Did you try my formula?

	A	B	C	D	E	F	G	H	I
1									Check
2		46,009	33,972		46,009	33,972		1.35	1.35
3		30,029	34,519		76,038	68,491		1.11	1.11
4		23,619	21,839		99,657	90,330		1.10	1.10
5		13,681	19,231		67,329	75,589		0.89	0.89
6		14,029	15,775		51,329	56,845		0.90	0.90
7		10,156	12,695		37,866	47,701		0.79	0.79
8		10,727	13,789		34,912	42,259		0.83	0.83
Sheet1

Cell Formulas
Range	Formula
E2:F2	E2	=B2
E3:F3	E3	=SUM(B2:B3)
E4:F8	E4	=SUM(B2:B4)
H2:H8	H2	=SUM(TAKE(B$2:B2,-3))/SUM(TAKE(C$2:C2,-3))
I2:I8	I2	=E2/F2

I don't know how you're getting your numbers in column H?

 

[nparrillo]

Did you try my formula?

	A	B	C	D	E	F	G	H	I
1									Check
2		46,009	33,972		46,009	33,972		1.35	1.35
3		30,029	34,519		76,038	68,491		1.11	1.11
4		23,619	21,839		99,657	90,330		1.10	1.10
5		13,681	19,231		67,329	75,589		0.89	0.89
6		14,029	15,775		51,329	56,845		0.90	0.90
7		10,156	12,695		37,866	47,701		0.79	0.79
8		10,727	13,789		34,912	42,259		0.83	0.83
Sheet1

Cell Formulas
Range	Formula
E2:F2	E2	=B2
E3:F3	E3	=SUM(B2:B3)
E4:F8	E4	=SUM(B2:B4)
H2:H8	H2	=SUM(TAKE(B$2:B2,-3))/SUM(TAKE(C$2:C2,-3))
I2:I8	I2	=E2/F2

I don't know how you're getting your numbers in column H?

I did try it. For example, your

Did you try my formula?

	A	B	C	D	E	F	G	H	I
1									Check
2		46,009	33,972		46,009	33,972		1.35	1.35
3		30,029	34,519		76,038	68,491		1.11	1.11
4		23,619	21,839		99,657	90,330		1.10	1.10
5		13,681	19,231		67,329	75,589		0.89	0.89
6		14,029	15,775		51,329	56,845		0.90	0.90
7		10,156	12,695		37,866	47,701		0.79	0.79
8		10,727	13,789		34,912	42,259		0.83	0.83
Sheet1

Cell Formulas
Range	Formula
E2:F2	E2	=B2
E3:F3	E3	=SUM(B2:B3)
E4:F8	E4	=SUM(B2:B4)
H2:H8	H2	=SUM(TAKE(B$2:B2,-3))/SUM(TAKE(C$2:C2,-3))
I2:I8	I2	=E2/F2

I don't know how you're getting your numbers in column H?

That worked, thank you.