<p>Is this what you want (in Stata)?</p>

<pre><code> clear all
 * Generate Data (n=5, T=20)
 set obs 100
 gen id = floor((_n-1)/20)+1
 by id, sort: gen year = 1990+_n
 gen x = rnormal()
 xtset id year
 * Convert
 gen t5 = floor((year-1991)/5)+1
 gen idt5 = id*10+t5 /* 10 can be 100, 1000, etc., depending on T */
 by idt5, sort: egen xbar5 = mean(x)
 *drop t5 idt5  /* drop if you want; you can also use tempvar */
</code></pre>

<p>If not, please clarify your question using an example.</p>

CC BY-SA 3.0
2016-10-20T09:44:21.733Z
2016-10-20T09:44:21.733Z
chan1142
<p>Use the <code>tssmooth ma</code> command. Use the <code>window()</code> option to control time periods to be included in the average.</p>

<pre><code>. use http://www.stata-press.com/data/r14/abdata, clear
. keep id year wage
. quietly keep if id == 1 | id == 2
. quietly xtset id year
. 
. ** moving average of 2 years before, 2 years after, and the current year
. tssmooth ma wage_moving_avg_5yr = wage, window(2 1 2)
The smoother applied was
     by id : (1/5)*[x(t-2) + x(t-1) + 1*x(t) + x(t+1) + x(t+2)]; x(t)= wage

. list

     +--------------------------------+
     | year      wage   id   wage_m~r |
     |--------------------------------|
  1. | 1977   13.1516    1    12.7643 |
  2. | 1978   12.3018    1    13.0242 |
  3. | 1979   12.8395    1    13.2773 |
  4. | 1980   13.8039    1    13.6206 |
  5. | 1981   14.2897    1   13.91592 |
     |--------------------------------|
  6. | 1982   14.8681    1   14.18503 |
  7. | 1983   13.7784    1   14.31207 |
  8. | 1977   14.7909    2   14.61597 |
  9. | 1978   14.1036    2   14.83472 |
 10. | 1979   14.9534    2   15.10716 |
     |--------------------------------|
 11. | 1980    15.491    2   15.37526 |
 12. | 1981   16.1969    2   15.81556 |
 13. | 1982   16.1314    2    16.0311 |
 14. | 1983   16.3051    2   16.21113 |
     +--------------------------------+
</code></pre>

<p>Note that this command takes what's available in the specified time period. For example, for year 1977, since two previous years are not available, it simply calculate the average of current year and the next two years.</p>

CC BY-SA 3.0
2016-10-25T20:23:00.070Z
2016-10-25T20:23:00.070Z
Paul
<p>I need Stata commands or Excel function to calculate the average over 5 years groups of the values in a panel dataset. In particular, this procedure as to take into account the presence of possible missing values (empty cells in Excel) and thus adjust the computation accordingly to the actual number of non-missing in the period.</p>

CC BY-SA 3.0
2016-10-19T13:22:16.513Z
Not 100% sure.it is something like this: You need to use the command tabulate. If you need to do average only on the last 5 years and not on whole period of the panel, use: tabulate "name of the variables" if "name of the time variable"=2016,2015 etc. (I don't remember the exact syntax for "if", so i just put a comma, but it could be a parenthesis)
Lex
CC BY-SA 3.0
2016-10-19T20:27:51.080Z
Excel's `AVERAGE()` function will ignore empty or non-numeric cells (though not error cells)
Henry
<econometrics><applied-econometrics><stata>
Averaging data over 5 years with Stata or Excel
user98139
# Averaging data over 5 years with Stata or Excel

(Asked by: username_0 on 2016-10-19)

I need Stata commands or Excel function to calculate the average over 5 years groups of the values in a panel dataset. In particular, this procedure as to take into account the presence of possible missing values (empty cells in Excel) and thus adjust the computation accordingly to the actual number of non-missing in the period.

**Tags:** econometrics, applied-econometrics, stata

> **Comments:**
> **username_1**: Not 100% sure.it is something like this: You need to use the command tabulate. If you need to do average only on the last 5 years and not on whole period of the panel, use: tabulate "name of the variables" if "name of the time variable"=2016,2015 etc. (I don't remember the exact syntax for "if", so i just put a comma, but it could be a parenthesis)  
> **username_2**: Excel's `AVERAGE()` function will ignore empty or non-numeric cells (though not error cells)  

---

## 2 answers

### Answer by username_3 on 2016-10-20. Score: 1

Is this what you want (in Stata)?

```
 clear all
 * Generate Data (n=5, T=20)
 set obs 100
 gen id = floor((_n-1)/20)+1
 by id, sort: gen year = 1990+_n
 gen x = rnormal()
 xtset id year
 * Convert
 gen t5 = floor((year-1991)/5)+1
 gen idt5 = id*10+t5 /* 10 can be 100, 1000, etc., depending on T */
 by idt5, sort: egen xbar5 = mean(x)
 *drop t5 idt5  /* drop if you want; you can also use tempvar */

```

If not, please clarify your question using an example.


---

### Answer by username_4 on 2016-10-25. Score: 0

Use the `tssmooth ma` command. Use the `window()` option to control time periods to be included in the average.

```
. use http://www.stata-press.com/data/r14/abdata, clear
. keep id year wage
. quietly keep if id == 1 | id == 2
. quietly xtset id year
. 
. ** moving average of 2 years before, 2 years after, and the current year
. tssmooth ma wage_moving_avg_5yr = wage, window(2 1 2)
The smoother applied was
     by id : (1/5)*[x(t-2) + x(t-1) + 1*x(t) + x(t+1) + x(t+2)]; x(t)= wage

. list

     +--------------------------------+
     | year      wage   id   wage_m~r |
     |--------------------------------|
  1. | 1977   13.1516    1    12.7643 |
  2. | 1978   12.3018    1    13.0242 |
  3. | 1979   12.8395    1    13.2773 |
  4. | 1980   13.8039    1    13.6206 |
  5. | 1981   14.2897    1   13.91592 |
     |--------------------------------|
  6. | 1982   14.8681    1   14.18503 |
  7. | 1983   13.7784    1   14.31207 |
  8. | 1977   14.7909    2   14.61597 |
  9. | 1978   14.1036    2   14.83472 |
 10. | 1979   14.9534    2   15.10716 |
     |--------------------------------|
 11. | 1980    15.491    2   15.37526 |
 12. | 1981   16.1969    2   15.81556 |
 13. | 1982   16.1314    2    16.0311 |
 14. | 1983   16.3051    2   16.21113 |
     +--------------------------------+

```

Note that this command takes what's available in the specified time period. For example, for year 1977, since two previous years are not available, it simply calculate the average of current year and the next two years.


---

