Charting Y-Values by Hour Blocks

[kelman17]

Hi all! This is a cross-post. Original: Charting Y-Values by Hour Blocks

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
I don't have privileges to include attachments. But here is my data.. there's not much. 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.
From original: 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!

Thanks in advance!

 

[tusharm]

I imagine someone will help you but here's something for you to think about.

You are sharing links to images hosted on a forum named excelforum, which requires registration and logging in before showing the image. So, a volunteer on mrexcel would have to create an account on that forum, log in, pull up your images, interpret them to understand what you want, and then provide you with a solution.

Contrast that with someone who explains what they want with a description of their issue. Who is more likely to get the help they seek?

Hi all! This is a cross-post. Original: Charting Y-Values by Hour Blocks

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
I don't have privileges to include attachments. But here is my data.. there's not much. 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.
From original: 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!

Thanks in advance!

 

[kelman17]

Thanks totally overlooked that.

 

[kelman17]

Hmm... I can't edit my original post. Here is the same thing with proper links to my images:

Hi all! This is a cross-post. Original: Charting Y-Values by Hour Blocks

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

Daypart Chronological	Start	End	Imp/Calls
5a-6a	5	6	20,730
6a-7a	6	7	16,510
6a-8a	6	8	23,947
8a-9a	8	9	28,487
8a-10a	8	10	28,269
9a-10a	9	10	29,377
9a-11a	9	11	15,217
10a-12n	10	12	21,111
10a-12p	10	12	27,949
11a-1p	11	13	17,079
12p-1p	12	13	18,675

<colgroup><col width="64" span="3" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

I don't have privileges to include attachments. But here is my data.. there's not much. 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

I'm trying to create a chart in Excel that is similar to this Gantt chart.
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.
From original: 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!

Thanks in advance!

 

[tusharm]

The Gantt chart has nothing to do with your data set.

What would be the desired result for your sample data?

Hmm... I can't edit my original post. Here is the same thing with proper links to my images:

Hi all! This is a cross-post. Original: Charting Y-Values by Hour Blocks

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

Daypart Chronological	Start	End	Imp/Calls
5a-6a	5	6	20,730
6a-7a	6	7	16,510
6a-8a	6	8	23,947
8a-9a	8	9	28,487
8a-10a	8	10	28,269
9a-10a	9	10	29,377
9a-11a	9	11	15,217
10a-12n	10	12	21,111
10a-12p	10	12	27,949
11a-1p	11	13	17,079
12p-1p	12	13	18,675

<colgroup><col width="64" span="3" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

I don't have privileges to include attachments. But here is my data.. there's not much. 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

I'm trying to create a chart in Excel that is similar to this Gantt chart.
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.
From original: 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!

Thanks in advance!

 

[kelman17]

Yes I should clarify! The Gantt Chart in my desired result is just a random picture from the internet. It is there to give a visual idea of what I am looking for. Sorry, I am trying to be lazy and not create picture showing exactly what I am looking for But I am essentially trying to create a similar Gantt Chart in Excel using my sample data.

For the x-axis of the chart, I am trying to use the "Start" and "End" times from my sample data. So each block on the Gantt Chart would show a start-end of a different row in my sample data.

For the y-axis, instead of having tasks like "Market Research" or "Define Specifications" (as in the picture of the Gantt Chart) I want to show the "Imp/Call" from my sample data. My biggest challenge has been getting the "Imp/Call" data to be shown at scale i.e. each data-point for "Imp/Call" should be at the right place on a scale of 1 to 30,000.

I really appreciate any help, thanks!

 

[kelman17]

Bump!

 

[p45cal]

Will something like this do? The first isn't sorted, the second is sorted by Imp/Call but the y-axis is just labels, not a scale.
ps. I see Andy pope has responded with a file in your cross post but it's showing 0 views.
<embed src="https://www.box.com/embed/79z59r4j3uk5fdf.swf" wmode="opaque" type="application/x-shockwave-flash" allowfullscreen="true" allowscriptaccess="always" height="800" width="466">

 

[kelman17]

p45cal, thanks for the reply. Your second chart is along the lines of what I'm looking for. But I am trying to find a way to put Imp/Call to scale rather than as labels.

Did you use a stacked bar chart to make those? I am not sure the stacked bar chart is the best tool to do what I am aiming for. Unfortunately it is the only method that I know of. If anyone knows another tool to create the same result BUT to scale, I would greatly appreciate it!!

 

[p45cal]

This?:
<embed src="https://www.box.com/embed/vzout8xglruiupu.swf" width="466" height="500" wmode="opaque" type="application/x-shockwave-flash" allowFullScreen="true" allowScriptAccess="always">
see also file here. It's a cheat! It is a stacked bar.