Charting Y-Values by Hour Blocks

[kelman17]

Hi all!

Context
I’m a media planning intern at a small marketing agency. Basically I choose TV programs to place ads on. The campaign is all about getting people to call the toll free number in the commercial. I want to analyze past TV placements in a region to track a metric over the different hours of the day.

Sample Data
input01.png
Please see attached. I pull the series of dayparts from existing data. I manually enter the start and end time columns to make it ready for charting on the x-axis.
The Imp/Calls is the metric that I want to track on the y-axis.

Desired Result
gantt chart.png
X-axis should be hour blocks.
The different tasks on the y-axis should be values on a scale. We are dealing with numbers in the 10 thousands.

Notes
The leading solution seems to be making a Gantt-style chart using a double-stacked Bar Chart. I would get that segmented look by formatting the bars to be invisible before the Start Time. I just can’t find a way to place my y-axis values on a scale.
I'm cross-posting this to MrExcel. I've read the FAQ, and I respect the value of everyone's time. On the other hand, I want to get more reach for my question. I will be sure to check both posts periodically and update status!
Here is the link to the cross-post: http://www.mrexcel.com/forum/excel-q...ml#post3491269

Thanks in advance!

[kelman17]

Bbbbbbbbbump

[Andy Pope]

See attached

[kelman17]

Thanks for your help Andy! I'm trying to put the "Imp/Call" data in order and at scale though. I was just saying on the MrExcel forum, I don't think a bar chart has the flexibility to do this. Unfortunately it is the closest way to do it that I can figure out. If you know of another tool/chart let me know!

[Andy Pope]

The order can be done by sorting your data.
When you say 'at scale' are you refering to the vertical axis?

[kelman17]

Hey Andy, that looks right! I may need to change the data ie change the y-axis data to average "imp/call" per hour, but that is all on my end. I have a busy day at work, so I haven't taken a close look at your spreadsheet yet. But how did you do that tho?


Anyway I need some more time before I can really examine your spreadsheet.

[Andy Pope]

it's an xy-scatter plotting start points. The lines are plus X-error bars using Custom values, which is the duration.

[kelman17]

I'm going to call this solved for now. The scatter plot definitely works, but I need to rethink what I want the result to actually look like. That's an issue on my end. Andy thanks a lot though! That chart definitely works.