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.
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.
« 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.
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]