Resetting a moving average at change in another value

[Alrik]

I have a worksheet that has the following data:

Year/State/County/Value/RA

The last column is a running average of the values in the 4th column.

Formula now:
E3: =IF(C3=C2;AVERAGE($D$2:D3);D3)
E4: =IF(C4=C3;AVERAGE($D$2:D4);D4)
E5: =IF(C5=C4;AVERAGE($D$2:D5);D5)
etc.

When the value of the county changes (column C), I need to have the moving average reset and begin counting anew. I can calculate the moving average up to here; and, I can have the formula enter the new starting value. But, is there a way to have the absolute value reset in the formula so that it begins with a new value on a change in column 3? Ideally, the last line formula would read:
IF(C5=C4;AVERAGE($D$42:D43);D43).

How can i set the absolute value from $D$2 to $D$42?

Just to make it extra challenging, here's a monkey wrench: The number of observations changes across counties; in other words, sometimes there are 44 observations (1970-2013), sometimes 30, or as few as 8.
Thank you in advance for your thoughts!

 

[NARAYANK991]

Hi ,

Can you upload a sample workbook ? What you want done should be possible.

Narayan

 

[Hui]

Alrik

Firstly, Welcome to the Chandoo.org Forums

Why not use the SubTotal Function
Select your data Table
Goto the Data, Subtotal Tab
At each Change in : Column C or County
Use Function Average
Add Subtotal to C
Ok