Stacked Chart to show value and status YTD

[neilo1969]

Hi All,

I'm trying to prepare a non-standard chart in Excel, and it appears it's not as starightforward as I first thought! I've successfully used online tutorials is similar cases, but this time I'm having diffculty even translating what I want to achieve into a meaningful search term. After coming up empty handed I thought I'd see if anyone on the site could point me in the right direction.

I'm tracking Saturday Overtime on a number of machines. For each machine I want to prepare a YTD summary in the form of a stacked chart, that would show the total cumulative number of hours worked with each month being shaded either red or green, showing if the value was above or below target.

I've already created a table that seperates out each month's overtime worked into two series, one for "at or below target" and a second for "above target" months. I've seperated them to hopefully use a green fill for under target and a red fill for overtarget. Upon inserting a chart, no combination I can find of switching rows and chart formats gives me anything like I'm looking for.

In a perfect world I'd have several stacked columns, one for each machine, but I'm more than happy to get even a single chart up and running and I'll adjust formatting to have several sepaerate charts displayed together.

It appears the chart I'm looking for isn't as so easy to produce. Does anyone have any thoughts or can point me in the right direction?

I've pasted in a screenshot of my tables below, but couldn't immediately work out how to attach a copy of the Excel workbook.

Thanks in advance,

Neil M

 

[Rijnsent]

Probably the easiest way: create a table (somewhere out of sight) with 24 rows: "Jan-over", "Jan-under", "Feb-over", "Feb-under", etc. and make a stacked bar chart with these 24 series. Next, color all series either red (under) or green (over). That should give you your chart.

 

[neilo1969]

Probably the easiest way: create a table (somewhere out of sight) with 24 rows: "Jan-over", "Jan-under", "Feb-over", "Feb-under", etc. and make a stacked bar chart with these 24 series. Next, color all series either red (under) or green (over). That should give you your chart.

Thanks Rijinsent, that's a more straightforward soloution that I'd condsidered, but it makes sence. I'll look to make an update and feedback on how it went.