Category Archives: statistics

Fascinating Stats on City of Chicago Employee Pay

Leave a reply
Ever wonder where all the money goes that the City of Chicago takes for running our lovely municipality? Recently elected Mayor Rahm Emmanuel took a bold step in transparency to post this one!


The reason for the skew towards $77k is that is about the pay for most of the employees who are police officers and firefighters. This appears to be a union-set wage, which doesn’t make sense, e.g. as a new police officer/firefighter makes just about as much (not much percentgae  difference in pay) as one that has been on the job for many years….and like all collective bargaining schemes there is no room for management to differentiate pay on performance let alone between competent vs. incompetent.

The reason for the sharp drop off at the bottom-right is that this includes part-time employees, interns and those that receive salaries for being foster parents, police cadets and others.



mean $73,829
median $77,238
mode $77,238
standard deviation $77,238
min $1
max $260,004


POINT OF REFERENCE
  • Estimated (mean) per capita income in 2009: $27,138
  • Estimated median household income in 2009: $45,734 (it was $38,625 in 2000)


It would really be interesting to see the Board of Education and teachers (not necessarily by name)!

How to convert hh:mm:ss to decimal minutes in Excel

10 Replies

Let’s say you are using Google Analytics and want to run some statistics against the Average Time Spent (why you may want to do this will be the subject of a forthcoming blog post). First, you’re naturally going to export the data into Excel since GA offers only the basics. Next, you are going to find yourself stuck and staring at one or more columns of hh:mm:ss time.

Why? Unless you convert this time format data into to integers with decimals you will not be able to analyze the data for any revealing statistics. Seems simple enough to do in Excel, just convert the time into a decimal format…but no.

Alas, I was shocked that there is no formula-level function that does essentially the opposite of the CONCATENATE function in Excel. Curiously, it is much easier to covert the decimal back to hh:mm:ss format. Thanks to the many folks that pointed me in the right direction.

  • Many folks suggested the Data>>Text-to-Columns function in Excel but this would have blasted away many other columns of data and there were several of these time columns that needed this.
  • Others suggested converting the time figure using the format feature; that also didn’t work.

The solution was using the HOUR, MINUTE and SECOND commands in Excel. It worked like this:

=((HOUR(A1)*60)+MINUTE(A1)+(SECOND(A1)/60))
01:32:56 (1 Hour , 32 Minutes and 56 Seconds) = 92.93 Minutes
[Yes, you can cut and paste the above formula!]

Pretty neat, eh? However, still haven’t found a way to do this with some kind of delimiter like a “SPLIT” in Perl.