0

I have a dataset that includes year, country, and population data. A small condensed sample is below.

I am trying to write an excel statement that would calculate the average population from 1980 - 1985 depending on the country. There are only two in the sample chart but the dataset includes over 145 unique countries. The Year column repeats a sequence from 1980 - 2014 for each country and I am having trouble deciding which function would be best to solve this problem. Any ideas?

enter image description here

2 Answers 2

1

enter image description here

F5=AVERAGEIFS(C:C,A:A,$E5,B:B,">="&$F$1,B:B,"<="&$F$2)
1
  • Thanks Akina I used your format and was able to create a drop down list using the data validation option after I extracted the individual country names "aaa" or "bbb". Then I linked your formula for the average to the drop-down list cell. it was AVERAGEIFS(Population, Country, $Q$9, Year, ">="&$R$11, Year, "<="&$R$12) and it worked like magic. thanks again. Sep 19, 2019 at 22:24
0

I would like to suggest a method, popular among professionals, known as Control Break method, which categorizes the data as well as create group Look.

enter image description here

How it works:

  • Exercise needs Helper Column.
  • Put =1 in Cell T70.
  • Formula in Cell T71, and fill it down.

    =IF(Q71="",T70,T70+1)

  • Enter this formula in Cell U70 & fill it down.

=IF(Q70<>"",AVERAGEIFS($S$70:$S$81,$T$70:$T$81,"="&$T70,$R$70:$R$81, ">="&R$64, $R$70:$R$81, "<="&S$64),"")

N.B.

  • Shown method avoids to repeat country name in respected column, which saves time.
  • Data looks attractive after printing.
  • Later on you may hide Helper Value.

Adjust Cell references in the formula as needed.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .