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 Kentu...
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")
=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!