Excel Charts

March 10, 2004 8:44 AM

Excel question: what's the best way to build a chart to display this data? (more inside)

I want to build a chart to show the pattern of sign-ups for a service over a time period of about 2.5 years. My data is one column, with each cell containing a date representing one sign-up. Some of the dates repeat, reflecting multiple sign-ups on the same day. Some dates over the period are not represented, reflecting no sign-ups those days. Ideally, the chart should show at a glance how signups were distributed over the time period. What's the best chart layout to show this? How would I build it in Excel?

posted by stupidsexyFlanders to Computers & Internet (3 answers total)

I think you need a pivot table.
posted by iceberg273 at 8:58 AM on March 10, 2004

I would make a histogram. First you should construct a list of "bins," which are basically the cutoff values in the mm/dd/yyyy format. Note that the bins work by counting the number of data points that are equal to or less than the bin. So if you want to count signups by month, you should have a list of bins with the last day of each month.

Then go to Tools>Data Analysis and select Histogram. Select your input range (the list of dates), the bin range, and the output range. You will then get a handy two column chart listing your bins and how many of the dates fall into each bin. Then you can chart up that data however you'd like. Traditionally, histogram data is shown as a bar chart.

I made a sample worksheet that I could email if you'd like.
posted by mbd1mbd1 at 10:03 AM on March 10, 2004

Response by poster: That would be great, mbd1 (if I may be familiar). Here's my email.
posted by stupidsexyFlanders at 11:24 AM on March 10, 2004