Charting number of entries in a 15-minute block

[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?

 

[Jon Peltier]

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/
_______

 

[BookLady]

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 ;

 

[Tushar Mehta]

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,D15), where A1:A8 contained
call data in my example and D15 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

 

[Tushar Mehta]

Hi blonde with a temper,

See my response to Mike.

--
Regards,

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