Chart labeling: durations in minutes and seconds

Question

I am attempting to chart the durations of phone interactions. They are currently entered as time of day values (I did not build the workbook), even though they are actually duration values, and Excel utterly refuses to build a chart with them, giving me a complexity error on an otherwise well-formed chart plan.

I am attempting to get a chart that displays these values as minutes and seconds in the Y-axis with the dates of the interactions in the X-axis. The problem is that in the Y-axis Excel (2010) shows a distinct preference for clear numerical values, and my data is currently entered as hh:mm:ss (directly, not calculated).

I want the chart to display how many minutes and seconds on average were spent on a specific type of call on a specific date. I have been able to convert one set of values on the sheet to a whole number using the SECONDS function and gotten at the very least a usable chart -- but apparently that only works with time values of less than one minute (60 seconds). I have another column I wish to chart that displays time in minutes and seconds with a usual minute value of 1. The MINUTE function coverts these figures to straight minutes with no second values -- not particularly useful for these values (because the distinctions I am looking to graph are in a range of several seconds).

Do I have a third option to get the values I seek?

Solution

It turns out you can do what you want with the built-in formatting options. Just plot the duration times as they are stored (time values). Right-click on the Y axis and select Format Axis | Number. Among the time formats is a standard format mm:ss.0;@. Select that and it will appear in a customization box. Edit it to read: mm"m"ss"s" and click the Add button. The axis labels will be formatted like 05m27s.

Comments

[dav]

Can you provide an example of what you'd like the chart to look like? I can think of at least 2 different ways to display this info (gantt and bar charts).

[Michael Hopcroft]

I can provide at best a general description of what I am attempting to convey in the chart. I am attempting to show the trend over time of how long the call takes or the length of time the caller has to wait before being connected to someone who can help them. The variations are very small -- in some cases as little as two to three seconds -- but I need to present a clear picture of the trend (are the wait times improving?). Normally I would use a line chart to show trends, but these values break line charts due to their format.

[fixer1234]

@dav - The problem isn't with charting the data; almost any chart type will work. The problem is with labeling the Y axis in a way that time values can be readily interpreted.

[Michael Hopcroft]

Actually I havfe also had an issue with the complexity errors, which are abnormal for a chart like this which is otherwise well-constructed.

[Jon Peltier]

Twice you've mentioned "complexity errors" without telling us what the precise error messages were.

[Michael Hopcroft]

The precise error was "data range too complex". This usually happens when the number of values for the X and Y Axes do not match. An example would be is there is an axis stretching from A3 to A12 and the other axis stretches from B3 to B13. This error prevents the creation of a coherent chart until the ranges are corrected. MY problem is that I get this error when I DO have an axis-to-axis match. And this confuses me since it prevents the creation of an otherwise well-formed query.