How to create an Excel histogram showing distribution of timestamps
July 17, 2014 12:43 AM   Subscribe

I have a spreadsheet containing timestamps, that I want to display as a histogram

I've installed the Data Analysis plugin, and I have need to visualise a spreadsheet of timestamps as a histogram, but Excel (2010) is automatically generating a bin range when I want to set it manually. The source data has times in hh:mm:ss format and the bin range I'm specifying has the same format.
posted by jedro to Computers & Internet (1 answer total) 2 users marked this as a favorite
 
Best answer: I don't think you need the data analysis plugin for this. Times in Excel are just real numbers between 0 and 1. Midnight is 0, 1:00 AM is 1/24, 2:00 AM is 2/24, etc.

So let's say you have some times in cells A1:A40. Put the bins in cells E1:E24. I used 1/24, 2/24, etc., through 24/24=1. But you could use whatever you want for the bins. So if you knew the times were all between 8 AM and 5 PM the bins would be 9/24, 10/24, ... 17/24.

Then use the array formula FREQUENCY. Highlight cells F1:F24, then go up to the input box and type in =frequency(A1:A40,E1:E24)

After this you have to press CMD+SHIFT+ENTER (at least on a Mac -- Windows may have a different convention). Then the frequencies will be in cells F1:F24 and you can use the charting functions to make the bar chart. You can also format cells E1:E24 as times.

Look at the documentation for the FREQUENCY function.
posted by Pararrayos at 4:41 AM on July 17, 2014 [3 favorites]


« Older Help me not live off Trader Joe's and Lean Cuisine...   |   Can you take too much activated charcoal? Newer »
This thread is closed to new comments.