Challenge: creating Herfindahl index for patent concentration

Dear people,

For my master thesis I need to create a patent concentration ratio. The idea is to get a number between 0-1, indicating how large of a part of a firm's patents are from one specific industry (segment). I have the patent classification numbers for 110 firms ranging from 5 to 100 patents. To create the index number, I would need to sum the squares of the shares of patents. Example:

A firm with 5 patents (e.g. A61K3, C02F1, C12N9, C12N9, C12N9) would have an index of ((1/5)^2))+((1/5)^2))+((3/5^2)) = 0.44

I have three colums for each firm, one with 3 digits (e.g. A61), one with 4 digits (e.g. A61K), and one with 5 digits (e.g. A61K3). Ideally I would want the Herfindahl index for all three columns for all firms.

Doing it as I tried (see attachment, numbers in red) would take too much time. If anyone would be willing to take a look, and could suggest a way to make the process easier, that would be extremely appreciated! I would even credit you in my thesis .

Cheers!

Fabian

	A	B	C	D	E	F	G	H	I	J
1	BvD ID	Patent Number		3 dig.	4 dig.	5 dig		No. Of patents		3 uniq
2	AT9030116187	C12N15/81		C12	C12N	C12N1		12		8
3		C12N9/88		C12	C12N	C12N9		12		1
4		C12N9/88		C12	C12N	C12N9		12		1
5		C12N9/0004		C12	C12N	C12N9		12		1
6		C12N15/1051		C12	C12N	C12N1		12		1
7		B01D61/027		B01	B01D	B01D6		12
8		C40B30/02		C40	C40B	C40B3		12
9		A61K31/05		A61	A61K	A61K3		12
10		C02F1/288		C02	C02F	C02F1		12
11		C12N9/0065		C12	C12N	C12N9		12
12		C12N9/0065		C12	C12N	C12N9		12
13		C12N9/1007		C12	C12N	C12N9		12

What does the 8 mean in J2? There are only 5 distinct patents in col D.

More generally, what do all the columns mean?

Thanks for taking a look!

A = The ID code for each firm
B= The patent numbers, firm AT9030116187 has 12 patents.
D/E/F = The patent numbers shortened. This is necessary because a concentration ratio of patents with 10 digits will not yield desirable herfindahl ratios.
H = Number of patents for that firm
J/K/L = The number of times a unique value is reported in columns D/E/F respectively. The first number in this row refers to the number of times that a specific/unique patent number appears for each firm. *See explanation below.*
N/O/P = The number of unique values as a percentage of total patents for a firm (in this case: N2=J2/H2 or P2=L2/H2).
R/S/T = The percentages of N/O/P respectively, squared.
V/W/X = The herfindahl index. It is the sum of squares for each of the three initial columns, that only differ in how many digits were initially taken in columns D/E/F.

*The 8, for example, is the result of counting the unique values for the first firm (the first firm being AT9030116187, with its patents in range B2;B13. It refers to the fact that there are 8 X C12 in column B. The '1' underneath (J3) indicates that there is 1 X B01.

I understand why J2 is not intuitive to understand, it is the main problem with my current method. I need to find a reproducable way to count unique numbers for each firm. The problem is that these numbers differ for every firm, as does the amount of patents.

The way to see that there are indeed 5 distinct patents for firm 1, is that there are 5 rows of unique numbers in column J. (8 X C12, 1 X B01, 1 X C40, 1 X A61, and 1 X C02). This method required me to visually seek out every unique number, in order to count their instances, which is very time consuming.

Any thoughts? It is greatly appreciated

The way to see that there are indeed 5 distinct patents for firm 1, is that there are 5 rows of unique numbers in column J. (8 X C12, 1 X B01, 1 X C40, 1 X A61, and 1 X C02). This method required me to visually seek out every unique number, in order to count their instances, which is very time consuming.

For this part try this formula and fill across.

Formula:

Please Login or Register  to view this content.

It returns 5, 5, 6.

Is this what you want for this part?

To return the values across J2:L7 try array entering this in J2 fill down and across until you get blanks.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

An afterthought.

This formula for J2:L7 is a little bit shorter. It is still array entered.

Formula:

Please Login or Register  to view this content.

I array entered it into J2, but when I then drag it down and across it produces:

8 8 2
8 8 2

The second, shortened formula produces:

8 8 2
8 8 2
2

Ideally it would return:

8 8 2
1 1 6
1 1 1
1 1 1
1 1 1
1

Please see the attached. Except for the last (re-aligned) 1 that formula returns what it should.

Edit

If I understand correctly, array entering only requires me to press CTRL + SHIFT + ENTER once right? Initially I mean; when I enter the formula into J2. When dragging down and across I don't need to do anything additionally?

That is correct, and no you shouldn't have to do anything additionally for that "block" of BvD ID. Please understand that each "block" of BvD ID will need to be re-entered with appropriate ranges.

However if there is a way to change those ranges dynamically to fill all 110 blocks in one formula it will require modification to that formula and some helper columns.

I haven't considered it, yet. Do you want me to?

I went ahead with it anyway. (Couldn't resist the challenges. )

By the way ... thank you for clarifications in post #3. That helps a lot.

I won't elaborate on each of the formulas in the attached. I will note that in column A I completed the list of BvD IDs. If you like I will explain a short cut method for doing this.
Just know that offset row headers share the same difficulties as merged cells. They are fine for summaries where cosmetics are important. In source data they are the devil's spawn, create havoc in formulas and should be avoided always.

Also in column G is a formula to return the initial row number of each block of BvD IDs. It is one of 3 helper columns. Once you are satisfied understanding what they do ... if it were me ... I would copy and paste values only back onto each range. It will reduce the file size.

This formula in J2 is array entered and filled down to L2633.

Formula:

Please Login or Register  to view this content.

I also adjusted formulas in N:P and R:T to ignore blank lines.

I am at a loss regarding the final formulas in V:X. Did you intend for those to execute only on the first row of each BvD ID block? I must confess my grasp of 'Herfindahl index' is a bit anemic. So I really didn't know what to do with those.

My, but that is a lot of manual work!

Thank you for the feedback. Glad to hear you solved it.

It's an interesting project. I learned some things.

Wishing you the best on your thesis, and happy holidays to you as well.