Calculate 5 years average

[Anonymous]

Hi Everyone,

I have a requirement to calculate 5 years average.

For example:

company inputs:

company A:

2014 = 5

2015 = 10

2016 = 15

company B

2012 = 10

2013 = 15

2014 = 20

company C

2009 = 20

2010 = 25

2011 = 30

company calculations (3-year average)

company A = 10

company B = 15

company C = 25

population calculations (only for companies with data from the past five years)

companies included:  A, B (at least one year for both companies was 2013 or later)

companies excluded:  C (all years are prior to 2013)

five year average = average of included companies = (10 + 15) / 2 = 12.5

LOAD * INLINE [

    RecNo, Date, Value, company

    1,     2014,  5,     A

    2,     2015,  10,    A

    3,     2016,  15,    A

    4,     2012,  10,    B

    5,     2013,  15,    B

    6,     2014,  20,    B

    7,     2009,  20,    C

    8,     2010,  25,    C

    9,     2011,  30,    C

];

Please advice on how to achieve this?

Thanks in advance

[Anonymous]

It is not working...