Monday, January 21, 2013

Military pharmacy costs and summing across spreadsheets

I was thinking of a way to write about a story I helped with recently, Soaring cost of military drugs could hurt budget, but it wasn't really a technical masterpiece. Again, this is an example of a data-driven story where the best work was done in reporting.

Our attempt (Jeremy Schwartz and I) to find out how much the military spent on pharmaceuticals proved quite difficult. There are at least three methods they use: The Defense Logistics Agency buys drugs from pharmaceutical companies for use in military hospitals and pharmacies. The Department of Defense health care system, Tricare Management Activity, provide prescription services through retail pharmacies, just like other health care pharmacies. Tricare also offers members prescriptions through mail order drugs.

We were able to get pharmaceutical sales from DLA and Tricare, but not the mail order drugs. The records were difficult to work, as company names and drug names were not consistent. Cleaning up the data was more than adjust name ... the bulk came through research to find out what companies merged with others or were subsidiaries. That was just work. I created a new column in the spreadsheet and Jeremy and I each spent time doing research on company websites and other sources to find combinations as best we could.

Both the Tricare and DLA worksheets have the
same rows of companies in the same order,  as do the columns.
Once Excel trick I did use was to total cells from multiple worksheets. This isn't all that rare for those who use Excel often, but it might be something a general reporter may not know how to do, so I thought I would explain it here.

In my example, I have three worksheets in my spreadsheet file: DLA, Tricare, Combined. I had company totals by year in each column for both DLA and Tricare.

On the Combined worksheet, I used the following formula to add the companies together:

=sum(Tricare!B2+DLA!B2)

You are using the values of the B2 cell from another worksheet by prepending it with the name of the worksheet and the exclamation point.

You can then copy the formula to other cells, like any other formula.

2 comments:

Richard C. Lambert 2:42 AM  

Reviews coming up in the future for these products so stay tuned. What have you been buying in the boxing day sales? Any great finds?malegra DXT

smart boy 5:33 AM  

I have been impressed after read this because of some quality work and informative thoughts. I just want to say thanks for the writer and wish you all the best for coming! Your exuberance is refreshing. pharmacy reviews

  © Blogger template 'A Click Apart' by Ourblogtemplates.com 2008

Back to TOP