Combo Chart

[jedilefty]

Hello,

I'm having trouble with creating a combo chart for the table below. The Internal, Website Goal and Prior weeks are to be displayed in a Line form. Period 1, 2, & 3 need to be a stacked column and the Actual Average a separate column next to the stacked column. That's where I ran into trouble. I can't figure out how to get the stacked column by the regular column. Also, if the Actual average can automatically change colors based on if the value was below in Internal Goal, in between Internal and Website, and if it's greater than both goals (this is a requirement but would be nice for show).

Can you please list the step by step instructions on the solution?

Finish Type	Internal Goal	Website Goal	Actual Average	Period 1	Period 2	Period 3	Prior Wk
Type #1	2	10	6	15	3	1	12
Type #2	2	10	12	10	8	2	17
Type #3	2	10	15	4	5	8	67
Type #5	2	15	15	12	2	1	23
Type #6	2	15	30	18	4	2	107

 

[awoohaw]

I don't think this is an available combo chart in the templates provided in excel.
I take that back, it looks like it may be possible. I was trying to do this the other day, but I could not get overlays correct.Let me look at what yu want to do.

 

[awoohaw]

Okay, I've never seen combo charts with 3 types. But, I was able to add all three to a chart. But the kicker is that the regular column for Average is 100% overlaid by te stacked column they cannot be side by side, which is what I think you want.

 

[jedilefty]

Okay, I've never seen combo charts with 3 types. But, I was able to add all three to a chart. But the kicker is that the regular column for Average is 100% overlaid by te stacked column they cannot be side by side, which is what I think you want.

I really appreciate your help on all this. That's where I got stuck too. I couldn't get them to where they were viewable in a decent manner. I'll be presenting these charts in a meeting with the executive team at my job and I've already gone through 3 various on how to present the data. If you could get them to work I'd owe you big time.

 

[awoohaw]

Maybe there is a way in VBA, although I've never done charts with VBA at ALL I'm on the road today, so I cannot even think of looking at this until this evening, I think the only way now would be to SUM the three in an aggregate calculation using clustered columns, then use invisible lines (with really big visible markers) for each component. is the chart design your idea or one of the management team?

 

[jedilefty]

Maybe there is a way in VBA, although I've never done charts with VBA at ALL I'm on the road today, so I cannot even think of looking at this until this evening, I think the only way now would be to SUM the three in an aggregate calculation using clustered columns, then use invisible lines (with really big visible markers) for each component. is the chart design your idea or one of the management team?

The chart idea is mine. I've been tasked with presenting this data in a simplified manner. I've done tables and other variations of charts but they were too busy. I think this is the simplest way to show all data points without having to look at too many numbers.

 

[awoohaw]

OK. My suggestion for the Period dilemma is to have a column for the Actual Average and three invisible lines with Markers & Labels for the 3 periods.
Exclude Internal/Website/Prior from the chart.

How do Internal Goal, Website Goal, and Prior week really compare to the Periods?
I did a chart earlier for you comparing Actual Average to Internal/Website/Prior earlier this week, so you have that chart available as well.

 

[jedilefty]

OK. My suggestion for the Period dilemma is to have a column for the Actual Average and three invisible lines with Markers & Labels for the 3 periods.
Exclude Internal/Website/Prior from the chart.

How do Internal Goal, Website Goal, and Prior week really compare to the Periods?
I did a chart earlier for you comparing Actual Average to Internal/Website/Prior earlier this week, so you have that chart available as well.

I was able to adjust the width of the columns so they're readable (along with color variations). So the periods are the average cycle times for orders, which tells the story of why the Actual Average hit or miss the Internal/Website Goals. That's why I need those and then compare to what the Average time was for the previous week to show if any improvement was made.

I'm going to present this for now and see what the feedback is.

I appreciate the help on those previous charts. It was requested to add the 3 Periods information to those, so I dropped the automatic color changing of the bars.

I'll let you know what the team says...wish me luck!!

 

[awoohaw]

yw & gl