The functions for Herfindahl index for mass data processing

Dear all,

I am struggling with a task which asked me to get the HHI index for 506 air transportation routes from the year 1994 to 2012. In other words, I need to calculate the Herfindahl for every route every year. The number under every year is the seats available provided by the operators and the market share should be calculated beforehand. Actually I know how to calculate HHI but it is the first time for me to use Excel for this kind of tasks. I want to know a easiest way (function) to work out it. Here I attach the original data file in excel and I will appreciate it if you can give me several examples for it.

Thank you very much for your time!

Best,

G. Han

Since you cannot assume that we know how to calculate a Herfindahl index, I would suggest that you explain to us how you would calculate the herfindahl index outside of Excel. Once we understand how to perform the calculation outside of Excel, then we will be in a better position to help you program this calculation in Excel.

As a new user of Excel, I would suggest that one of your most useful help files will be a list of functions available in Excel: https://support.office.com/en-us/art...rs=en-US&ad=US Just as it is important to learn keywords and statements and such when learning other programming languages, one key element in learning to program spreadsheets is becoming familiar with a spreadsheets functions.

According to wikipedia, the Herfindahl index is simply the sum of the squares of the market shares of each firm H=sum(marketshare^2). So, it seems that the first step in calculating a Herfindahl index will be to compute the market share of each airline. I expect this will involve some SUMIF() or COUNTIF() functions, depending on how you define market share.

Once you have market share calculated for each airline, it should be a simple =SUMSQ() function.

Hopefull that ill help you get started.

So it looks like the first quantity we need is the number of seats supplied by each airline, is that correct? are these the quantities in the Y1994 etc. columns?

Another feature of Excel (and most database type programs) is a pivot table. A pivot table can take a "database" like this, and summarize it based on your chosen criteria. In the sample file attached, I created a pivot table that summarized Y1994 (year 1994?) by operator code. I have it sum the Y1994 column by operator code, it includes the total at the bottom. Then, if I've understood correctly, market share is each operator total divided by this grand total. The Herfindahl index is computed at the bottom, and is simply the sum of these squared (using the SUMSQ() function).