Stacked bar chart over time (on a 2nd axis?)

[SHW2022]

Hi all,

I have created a line chart in Excel that shows me a company's share price over a 10 year period. I then have two further lines that show me a "Target" price and a "Maximum" price.
What I would like to do is create a horizontal stacked bar chart (ideally overlaid on my original chart so I have just one chart but if not possible then happy to have two) that corresponds to whether or not I am below target, between target and max, or above max.
So for example for the first three months my data is below target so I want the first "stack" of my chart to be red, when I cross over target and am now between target and max I want my stack to be amber. I've attached a really crude drawing to this post.

What I have done so far is to create an IF statement to show me if I'm above/between/below and return a 1/2/3 accordingly (could be any value). I think then to create my "stack" sizes I need to count this array until it changes to a different number and then count from there until it changes again but I'm just not sure how to do this and/or if there is an easier way. I tried doing it manually but I have over 4,000 data points so was very long-winded!

Really quick example array:

1
1
1
1
2
3
3
2
1
1

So my stack sizes would be: 4, 1, 2, 1, 2 - how do I get these numbers without counting manually?

I hope that makes sense but happy to clarify anything!

 

[Max1616]

Are you looking for something like this?

If so, you'll need a data set set up like this:

Your selected data should look like this (Should just select the corresponding column for each series):

Then click the graph and go to the design tab on the ribbon, and click "Change Chart Type"
On the next screen, go to the bottom section and choose "Clustered Column" for the Over/Between/Under series. And check the "Secondary Axis" box:

Exit this screen.

Next you need to right click one of the bars on your graph, and click "Format Data Series". And then slide the series overlap to 100% and the Gap Width to 0%:

Finally, you will update the color of each series to the color you'd like it to show (Under = Red, Between = Amber, Over = Green).

I hope this helps!

 

[Max1616]

I forgot to mention! You'll also need to change the secondary Axis maximum to 10 (The smaller this number, the larger your Over/Under bars will be).

 

[SHW2022]

This looks absolutely perfect!! Thank you so much. I'm not back at my computer now until Monday so will let you know how I get on then.

 

[SHW2022]

I just ran this now and it was exactly what I was looking for, thank you so much!!

 

[Max1616]

Glad I could help!