Microsoft Excel

PingBack from http://geeklectures.info/2007/12/17/intraday-time-series-charts/

Sometimes the chart view can be aided by using 100% Y Error Bars to see the height of the lines better.  I would make the line a faint grey color  to compensate for adding a chart element.

Using a horizontal bar as the data point marker may also help.

Quote:

<<Also, notice that there are gaps in time where no one is waiting.>>

Connecting the data points in this case gives erroneous information.

Actually, connecting the data points is never a good idea with this sort of chart. There's no functional relationship between the number of phone orders at, say, 11:30 and 11:31. They're separate data points. They may be correlated, but that's a different concept.

As for the x axis, a wee bit of spreadsheet USER (as opposed to developer) experience would recognize that times are bins. In this example, the bins are single minutes. Use a chart range that includes EACH & EVERY minute from 11:00 AM to 4:30 PM (or whatever), and use formulas to pull in the number of callers in each minute, with no callers represented either as text or #N/A. Then a simple line chart using that derived range would generate the final chart above.

Since Office 2007's charting engine is apparently too stupid to interpret dates/times, and since it appears it may take as long for this to be fixed as it took for Excel to get colored worksheet tabs (9 years from Quattro Pro 5 introducing them to Excel 2002 providing them), it's a lot easier adjusting the Line Chart's x axis than screwing around with scatter chart settings using date/time serial numbers.

Are low information content/negligible USER experience content really the ideal filler for this blog?

BTW, wouldn't it be a better use of the Excel developer team's time to fix date/time interpretation than writing low information content blog postings?

Thanks for the feedback.  Sorry to hear that this topic isn’t one that is helpful for you.  Please keep in mind that Excel has a very large user base which we consider when selecting topics for the blog.  We have gotten direct feedback that these “help” articles are beneficial to a wide variety of users - though not each one will appeal to every person.  

I’m not sure I understand your comment about no functional relationship between the number of calls and time.  The number of calls (Y axis) is a function of time (x axis).  At least that has been my experience whenever I wait too long to order takeout.  The call volume is also influenced by other time related aspects such as the day of week, the season, and special events like a football game letting out.  As for the difference between 11:30 and 11:31 this isn’t an important aspect given the intent of the analysis; e.g. staffing the phone line with enough people for the entire day.  The purpose of the chart was to help expose general patterns of call volume activity like peaks and valleys which is a classic use of a line chart.

It is possible to achieve the same chart using the approach that you described by inserting #N/A and using a normal line chart.  However, this process can be very time consuming, error prone, and laborious for large data sets.  Depending on your units of measure (minutes, seconds), you may need to insert hundreds or thousands of #N/As at irregular intervals to ensure a uniform sequential time series.

Picky: while the size of the queue varies over time, that doesn't mean the data points are functionally related. It's the means of the underlying counting process distributions in sequential time periods may be functionally related.

Your chart, with lines between each point, implies there's a functional relationship between each pair of points, but if there's any significance to the different number of calls at, say, 11:44 and 11:45, there'd also be some significance to zero calls at 11:43.

Anyway, this is a situation in which a TREND LINE not necessarily running through ANY of the data points would be MUCH MORE MEANINGFUL than connecting the data points. Excel 2007 does still provide trend lines? If you're the data visualization guru, you do have some idea when and how to use them?

Back to Line charts. They allow one to group the data using the category axis scale settings

-Number of categories between tick-mark labels

-Number of categories between tick marks

Myself, I'd set the former to 30 and the latter to 15, so labels every 30 minutes but tick marks every 15. Nice simple numbers of minutes. Less typing than for scatter charts even using Excel 2003, which can interpret date and time entries as chart axis scale settings.

As for my setup being time consuming, name the original data range Data. Define the name hm as =1/24/60/2. In another worksheet enter 11:00 AM in A5, the formula =A5+"00:01:00" in A6, and fill A6 down as far as needed. Enter the formula

=IF(ABS(VLOOKUP(A5+hm,Data,1)-A5)<hm,VLOOKUP(A5+hm,Data,2),#N/A)

in B5. Double click on B5's fill handle. You now have the necessary Line chart range. How time consuming? How error prone? And, especially, how error prone RIGHT NOW compared to having to enter time serial numbers?

Re hm: it's the time serial number for a half minute (half of 1/60th of 1/24th of a day). It's necessary because lookups often (usually) fail when time values are involved. So if times appear as hours and minutes, and they're sorted in ascending order, safer to add a half minute to time lookup values.

These posts are amusing. It's nice to see how little practical experience the Excel development team has using Excel. Y'all might want to consider letting Excel MVPs write articles like this.

Harlan, you are reading a wrong blog :) Go here: http://www.dailydoseofexcel.com/

how do i get real-time data into excel. is it still via COM? DDE? please tell me you have something better.

oh by the way excel 2007 is rubbish. i have been using excel for 6 years and this latest version is a real step back.

what has happened to microsoft? if you want stuff to work on, heres an idea - make it faster. vista takes 20 minutes to boot up and my life is too short.

We've beefed up our support for bringing data into Excel from databases and file based data sources(with new top-level connection objects, and improved support for OLAP data) but we haven't made any changes or improvements related to bringing in real-time data in Excel 2007.

When will Microsoft provide us with a proper charting engine? What are the plans  for excel 2010? Are we getting a better chart engine or what? We appreciate the work done in excel 2007 but it is grossly inadequate. What is currently available is shocking, the charts are misleading (“3D” charts), full of chartjunk (all excel charts) some are outright nonsensical e.g. the pyramid charts.

And why must we be charting heroes to use the damn charts?! We want charts that work not charts that need some complicated workarounds!!! When are getting true 3 D charts, dashboard charts, water fall charts, etc?

When I am looking at your chart with the two red markers I was wondering how you got the data points to hyperlink? Is that an option in excel or ???

I want to create a chart and have it drill down to more charts / data.  Any ideas?

I hope you heave ideas for the question of Patty, because I'm looking for this solution to.

T. Kwetane,

There are many things in the works at Microsoft but I can't comment at this time on the specific details.  For the next release of Excel, we are focusing on some of the primary concerns expressed by customers.

Patty,

In my example, there isn't a hyperlink on the red markers.  However, there are a few different ways you can hyperlink to other charts/worksheets or drill on a chart.

Option 1

•Excel 2007 doesn’t allow you put a hyperlink on individual chart elements such as a data point.  However, it does allow you to put a hyperlink on the entire chart area.  Here are the steps:

1. Click on the chart area

2. Click on the Insert tab in the ribbon

3. Click on the hyperlink button in the Links group

4. Enter the relevant address and click OK.

•Now when you click on the chart, you will be navigated to the address specified.  This approach is ok for when you want to provide supplementary information about a chart or provide a link to data that is in another workbook.  To further edit the chart, you will need to remove the hyperlink because the hyperlink prevents you from clicking inside the chart.  To remove the hyperlink, ctrl+click on the chart and then click on the hyperlink button in step 3 above to remove the link.

Option 2

•As noted above, Excel 2007 doesn’t allow you to put a hyperlink on individual chart elements.  However, you can insert a shape or textbox and put a hyperlink on those items.  For example, you could put a textbox label next to a data point or put the shape right on top of the data point with a hyperlink.  The steps for this are largely the same as option 1, except the shape is the item clicked on in step 1.  Keep in mind; however, that the inserted shape or textbox are not anchored to the data point.  If the data changes, the shape or textbox won’t move. This can be addressed through VBA but I don't have an example readily available at this time.

Option 3

•There are several other options called out in Jon Peltier’s article on hyperlinks: http://peltiertech.com/Excel/Hyperlinks.html

Option 4

•To drill down on a chart, you can create a PivotChart with hierarchical data which provides similar drilling capability as PivotTables.

Thanks for the information. Yes, I know how to hyperlink but I was wonderful about the above graph? Do you produce the link in pdf?

Patty,

If I understand you correctly, you are referring to the actual chart in this blog.  We use a blog tool that takes care of creating the hyperlink.  As far as I know, PDF isn't involved.  If I'm still missing your point, please post your email address and I'll correspond directly with you.

Yes Scott that is what I was talking about. Thank you.

If both columns in the above example are in the format h:mm then selecting an X Y Scatter chart yields a two series line graph.  Why?

Is there a reason why you would use a time format on the second column?  The “number of customers waiting” isn’t a time unit.  As for why there are two series generated, the heuristics for determining the series don’t account for this data structure.

"For the next release of Excel, we are focusing on some of the primary concerns expressed by customers"

You certainly  would not divulge the efforts at Microsoft, but may you list the primary concerns?

My primary concern is units of measure.

A very close second are sheets that are individually placeable on a page and are of limited size - instead of automatically being X columns by Y rows, they are only as big as the data they hold. This way a single page could have multiple work sheets.

What would make this complete is to have the column and row names be changeable so that one could create a statement like "=total(sales!extended)" where 'sales' is a sheet and 'extended' is a column on that sheet that is titled 'extended' where the user can see it.

With units, some values could be dollars, others yen, others euros and the total would be whatever currency format the user asked for. If one of the entries does not have a currency unit, the total would fail. Certainly for currency the conversions are not static and would require a means to update them, but for many other units - metric vs imperial, the units are static.

@Scott Ruble

I believe the point that Harlan was making about the functional relation is that the lines do not represent the change with