average calculation, while matching years and regioncodes

[Kaasdevries]

Hi all,

I have a dataset which has regioncode's, years and growthrates (from 2014-2018). The original set is about 10.000 rows, but i've attached a simplified 14.

Question:
I would like add a new column which calculates the average growth rate from 2014-2018 for the given observation, matching with the correct regioncode:

1: (2014+2015+2016+2017+2018)/5

In the dataset (see below), I have shown the preferred result. So if the observation has a year of 2014-2018, it should show the average. If the year is 2012 or 2013, the cell should be empty.

example.xls

I have tried some combination of averageifs( ), but I could not make it work.

Best,

[oeldere]

You better added an xlsx file, since you use 2021.

With the data in a table.

Used formula to change text into value for the year.

With a formula to determine the criteria.

After that with a Pivot table.

See the attached file.

[CARACALLA]

E2=IF($A2<>"",IF($C2>$G$1,AVERAGEIFS($D$2:$D$100,$C$2:$C$100,">"&$G$1,$B$2:$B$100,B2,$A$2:$A$100,A2),"empty"),"")

Copy down

type year in cell G1

[wk9128]

Cell E2 array formula , Drag down

Please Login or Register  to view this content.