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!
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!