I'm usually not bad when it comes to Excel, but this one has me stumped.

I have a list of patents (about 600) from about 50 different companies. Each of them expire 20 years from issue date and all of them have different issue dates. I have the issue and expiration dates.

I'm looking to make a table that will allow me to see the changing composition (percentages) over the next 20 years that each company's patent will take up in the pool of 600 (assuming no new patents are added), accounting for viable life. Basically, by the end of the table (at around 2030), all the patents should have expired.

Ex. If Sony has 300 (out of 600) now, they comprise 50%. Next year, they might lose a few and their percentage out of the new total patents would change correspondingly.

 

Brute force: make a table with the patents listed one per row, and add a column for each date that you want to display the content for. Add a formula that resolves to 1 if the column date is greater than the issue date and less than the expiration date for the patent in the row. Make a grand total row at the bottom that sums them up for each column, and graph only that row.

 

I think I may just have to do that. I'll probably have to pivottable the thing to summarize. Not gonna be a fun day.

fuuuuuu. i like the formula thing, thanks!

 

Columns: Company, Patent, Issue Date, Exp Date, 1 col per year for 2010 through 2030

Populate the first four columns from your data. Populate the last 20 columns as Mike said: Sort by company.

Subtotal by company, summing the numbers generated in the last 20 columns.

Calculate percentages by company.

 

Need a formula.

Column A has expenses listed as $ (1234.56)

Column B has expenses listed as $ (4567.89)

I need a formula to calculate the percent change from A to B.

Will =(A1-B1)/B1 work?

 

If A is the original amount and it's gone up to B, your formula should be =(B1-A1)/A1

 

or 269.99% increase

 

for % change an easy way to remember your formula is: =(New-Old)/Old

 

Awesome, thanks!

Sometimes the amount went up and sometimes down, but this mnemonic really helps.

 

Hopefully I understand your requirement.. Is this what you looking for? Total active patent of company A of a year/ Total active patent of a year?