• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a column chart of call duration breakdown

Stout

New Member
Hi guys,

I am trying to create a column chart of call elements, showing what happened in the call at what point. I have attached an example file of what I have attempted so far (call1), and what a finished call would look like (call2).

Because the order and frequency of call parts change on each call, I think that a standard excel graph would be unworkable, as such I have been attempting to use conditional formatting, on a row of cells, where each cell represents a second. The calls are recorded on the Calls tab, then pulled into the table on the left side of the Summary page (this would be hidden) to make all the data in one line, and create a cumulative sum of the seconds. Then the value of the call element is matched against the Legend and using conditional formatting, all numbers in the graph are coloured up to that second.

The idea being that this would be a template that could be adapted depending on the type of calls or what was attempting to be tracked - so for example the legend could be changed to include 'Promotional Offer 1' and 'Promotional Offer 2' if that was something looking to be recorded.

This solution would work, but for each call section it requires 20 rules, and there needs to be between 10 and 15 sections. Lots of rules which are tedious and hard to manage!

I've included Call 2 which I have coloured manually to show what an actual call could look like, with multiple sections of the same colour (if someone is put on hold twice for example).

Does anyone have any suggestions or examples of a more elegant and simple solution? Could an image be used for each section which is stretched and coloured for the duration/type? I had also started to try to setup a macro to automate creating the rules, but again was unsure if this was the best approach.

Any help would be much appreciated.

Thanks!
 

Attachments

  • Call Duration Graph Example.xlsx
    313.1 KB · Views: 9
It's a bit slow on the initial rendering, but here's one way to make the chart w/o using an actual chart. I moved the formulas to a different sheet, and use the REPT and concatenate abilities to tell XL which group we're in.
 

Attachments

  • Formatting Chart.xlsx
    538.2 KB · Views: 14
Back
Top