Forum Discussion
Average values long panel data
Hi Guys,
I got a table in the following long panel format:
| STATE | YEAR | VALUE |
| Alabama | 2004 | xx |
| Alabama | 2004 | xx |
| Alabama | 2005 | xx |
| Alabama | 2005 | xx |
| Kentucky | 2004 | xx |
| Kentucky | 2004 | xx |
| Kentucky | 2005 | xx |
| Kentucky | 2005 | xx |
...
What I need is another column providing the respective average value for each year and each state, i.e. for the first two rows it would be the average of the first two values.
I tried using AVERAGEIFS function but I would do it for each year and each state individually which doesn't make sense because I got quite a large amount of data.
Do you guys have any smooth way to do it for the whole column?
Additionally I need the same thing for only non-zero values and another column for the max. value for each year and each state.
I'd appreciate your help very much!
Kind Regards
Marco
3 Replies
- You should be able to use AVERAGEIFS and MAXIFS for what you want and the structure can be either A1 style or table structure style. For example you could have
=AVERAGEIFS(C:C, A:A, A2, B:B, B2) or
=AVERAGEIFS(Table1[VALUE],Table1[STATE],Table1[@STATE],Table1[YEAR],Table1[@YEAR])
for non-zero you have 2 things to consider: do you mean actual '0' values or blanks because the above formulas ignore blank values but do include '0' values. To exclude '0' values just add another 'IF' like
=AVERAGEIFS(C:C, A:A, A2, B:B, B2, C:C, "<> 0")- thank you!! that worked, I just had a small mistake in my definition of the command!
