Plot two series in same graph selected by "Drop Box" graph title

[Ochimus]

Apologies if this has been "done to death" on here already, but if so I have missed it.

Attached 160201 Two element graph.xlsx has first graph using Cols C and D to show the "start and end" measures expected.

Second graph uses Cols E - M, and shows actual measures taken periodically.

Third graph shows what I am trying to produce (the red line is just that, but needs to be real data form C and D)

I also need the Horizontal axis to start and end with the Headings in C and D, with the others in between.

And I need the Graph Title to be the "concatenated" names in Cols A and B, ideally as an "embedded" Drop List in the graph title itself, but can't find anywhere how to do that!

Any pointers or solutions accepted gratefully as ever.

Ochimus

[humdingaling]

you are actually trying to do a lot of little technique/tricks into one thing

first and easiest one

need the Graph Title to be the "concatenated" names in Cols A and B, ideally as an "embedded" Drop List in the graph title itself, but can't find anywhere how to do that!

have a helper column which concatenates Col A and B together and then link the title to that cell

ie in column N put formula

Please Login or Register  to view this content.

then setup a Drop list (Data validation) listing all the combinations then link the title to the DV cell

in attached example i have placed it in cell I10

[humdingaling]

made another version where i moved things around and made more comments


the red line is actually a X-Y scatter graph
http://peltiertech.com/Excel/Charts/...orzSeries.html

this link describes in detail how to do it

essentially go into select data screen and add another series
change that series into XY with straight lines

the rest is remapping the graph plots with the DV (Data Validation) setup earlier

[Ochimus]

humdingaling,

That's almost perfect, and I'm really grateful.

The one challenge left - which I can't work out from a quick look at the link you kindly provided - is that the red "Expected" line has to start before the Blue one. The horizontal axis has to begin with "Age on entry" with the red line showing the opening "score" in Col C.

The tests in Cols E onwards then start subsequently as you captured them in the attachment.

The red line then has to end beyond the last rating in Col M, with the Axis finishing with "Age at end of Year 10" and the value in Col D.

Hopefully inspiration will strike when I return to this in the morning, and I'll be able to close this out!

Ochimus

[humdingaling]

if you want to change the start and end position of the red line
change the 1 to 0.5 and 9 to =COUNT(D14:L14)+0.5

ps might want to get rid of the data labels which i added then...it looks cluttered with them on

[Ochimus]

humdingaling,

Just logged on to find your prompt response.

Thanks for showing me how to extend the red line.

But how do I get the data labels for that red line onto the same horizontal axis as the blue one? So the graph will read "Age of Entry" under the first red measure, then the intermediate statements there already, and end with "Age at the end of Year 10" under the second red measure?

I'm certainly keeping the data labels. Give an immediate indicator of differences!

Ochimus

[humdingaling]

after you add data label into chart, you can manually move data labels to any position you want
however it just will not correlate with the position of the line (if you know what i mean)

so it really will not paint an "accurate" position of the label in the chart

[Ochimus]

humdingaling,

Immediate apologies. I was convinced I responded to you, but obviously not!

You showed me how to have the two series on the same chart. What I am still trying to work out is how to "merge" the data labels from both series into the same row under the horizontal graph, so the labels "open and close" with the text from the red "flight path", and sandwich the labels from the blue assessment series.
If I can't get them all in order in one line, can I show the "red" labels as a separate row below the assessment labels?

Ochimus

[MrShorty]

What I am still trying to work out is how to "merge" the data labels from both series into the same row under the horizontal graph, so the labels "open and close" with the text from the red "flight path", and sandwich the labels from the blue assessment series.

It is not clear to me exactly what you want here. If I understand, and if I can suggest a different approach than the line/XY combination chart being looked at.

As with a lot of charting questions, I find that how the data is arranged in the spreadsheet is important. My first thought on a problem like this is to arrange the data like this:

Please Login or Register  to view this content.

the first row, naturally, represents the horizontal category axis data, and each row is for the 2 data series. The blank entries will not show up, though you may need to adjust the "hidden and empty cells" settings (Design tab --> Select Data dialog) to get the red series to connect the two data points. This approach will make "age at entry" the first category on the axis, "age at end of year 10" is the last category on the axis, and the blue categories are in between. In addition, this seems simpler than the combo chart you have been working on.

If that doesn't help, or doesn't accurately represent what you want, you are free to ignore. If I have understood correctly, give this a try and see if this works.