Panel data calculation in single column.

Dear all,

my panel data set consists of 28 countries for which I have one variable on an annual base for 18 years (1998 until 2015). The data set is strongly balanced though there are some data gaps. Column A consists of all countries, column B of the years and column C the data for the variable.

For each given year I would like to calculate the difference in the variable for all country-pairs. For example:


Name: Unbenannt.png
Views: 15
Size: 17.3 KB


In this example I am interested in the difference in the GDP between Germany – UK, Germany-Belgium and UK-Belgium in the year 1998. In addition, I would like to calculate the differences for those three country-pairs for the years 1999 and 2000. However, I do not want to calculate differences across different years.

Can you help me please to compute those differences? I also have to make sure that Excel computes only differences if data is available for both countries instead of filling gaps with a value of zero.

Thank you very much.

Kind regards

How about this. In B1 of sheet2 is a dropdown menu of the year (1998 to 2015)

In Col A is unique list of countries (A4:A...), In Row 3 is Unique list of countries (copy column A and transpose it)

So in B4 copied down and right

=IF(B$3=$A4,"-",SUMIFS(Data!$C$2:$C$10,Data!$B$2:$B$10,$B$1,Data!$A$2:$A$10,$A4)-SUMIFS(Data!$C$2:$C$10,Data!$B$2:$B$10,$B$1,Data!$A$2:$A$10,B$3))
Does that work for you?

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )