# Averaging data over 5 years with Stata or Excel

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.

• 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 Oct 19 '16 at 13:22
• Excel's AVERAGE() function will ignore empty or non-numeric cells (though not error cells) – Henry Oct 19 '16 at 20:27
• Perhaps you can share your data. From your written description alone it's not really clear what you want to do. – Kenny LJ Oct 20 '16 at 14:16

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 */


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.