Charting number of entries in a 15-minute block

  • Thread starter Thread starter Mike Cugley
  • Start date Start date
M

Mike Cugley

I have a spreadsheet I update with each call I take, and I'd like a
chart of how many calls I take in each 15-minute block during the day.
Each call is a separate row in the sheet, with a timestamp in column
C.

Suggestions?
 
Mike -

If the Analysis Toolpack is installed, use its Histogram tool. First,
make a list of bins, which would be the ending point of each 15 minute
period. Then select Data Analysis from the Tools menu, and choose the
Histogram tool. Select the input range, bin range, and any other
options, and do the analysis.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
mike going to sort of hijack your thread here.

Jon found the histogram thingy. But the information needed is how t
set up the bin thingies. Tried to do this a couple of years ago an
finally gave up in frustration.

You are explaining this to a redhead and redheads are just blondes wit
a temper. So please explain as if to the stereotype blonde ;
 
You can also do this without the Histogram tool.

Suppose your call times are in A starting with A1.

Suppose your first time interval is <= 10:00. Enter 10:00 in D1. In
D2, enter =D1+TIME(0,15,0). Copy D2 as far down D as needed. In my
example, I went to D5.

Select the corresponding cells in E, i.e., E1:E{last data row in D}

Array enter the formula =FREQUENCY(A1:A8,D1:D5), where A1:A8 contained
call data in my example and D1:D5 contained the times.

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top