With thanks to Jon Peltier, Tushar Mehta, Laurent Longre, … and those many posters whose solutions and ideas have helped me over the years -- hope this is of some use to you or others.
I needed to use a Line chart with a category X axis, and wanted to graph extra series whose X values were defined for only a subset of the category data. For example, if the primary category data is the alphabet entered into a vertical range named “wsrng_AllXs”, perhaps the extra series are the vowels in order and their vowel-sequence number wsrng_Subseries1Xs={“A”;”E”;”I”;”O”;”U”} and wsrng_Subseries1Ys={1;2;3;4;5}; I was willing to enter/display those two small ranges in the worksheet. Of course, the real goal was to allow arbitrary categories, arbitrary lengths, etc. Your sub-series data could be anything that fits that 2-column by n-subset-rows format; you might be adding horizontal or sloped lines or arbitrary values across part or all of the graph.
One way to do this is to create and graph an additional worksheet range “wsrng_FilledSubseries1Ys” of that same 26-row height, putting the matching Y values in their appropriate rows (“A”;”E”;”I”;”O”;”U”), and #N/A’s in the other rows (eg, Category Axis Tricks for Line and Area Charts - 1 - Peltier Tech Blog). Another way is a secondary X axis.
But I wanted to use the primary X axes and didn’t want to create extra tall columns in the worksheet for each of these extra subset series.
My plan was to create a defined name formula “defname_FilledSubseries1Ys” to return the desired tall array, and use that as the Y part of the subset-series definition in the chart. A number of approaches didn’t work because the chart seemed to “EVALUATE” the array formula and reduce it to the single first element of the array. Maybe others can get them to work, but for that reason I gave up on VLOOKUP, INDEX/MATCH, INDIRECT, and udf’s; array functions entered directly into and graphed from a worksheet range succeeded, but failed when EVALUATE-d into a worksheet range or displayed in the chart.
What eventually (!) worked requires only a single defined name formula for each subseries that you want to add to the graph:
defname_FilledSubseries1Ys, which refers to:
The sub-series formula on the graph is then something like:
At one point I thought I had to wrap using TRANSPOSE(TRANSPOSE(N(OFFSET, but that doesn’t seem needed. Also, I’ve just started using it (Excel 2013 32-bit; Win7 64-bit), so there may still be surprises to come. But this was enough to get me on to using the solution to add the subseries I needed for the project. No biggie perhaps, but this doesn't require a macro-enabled worksheet.
Thad
I needed to use a Line chart with a category X axis, and wanted to graph extra series whose X values were defined for only a subset of the category data. For example, if the primary category data is the alphabet entered into a vertical range named “wsrng_AllXs”, perhaps the extra series are the vowels in order and their vowel-sequence number wsrng_Subseries1Xs={“A”;”E”;”I”;”O”;”U”} and wsrng_Subseries1Ys={1;2;3;4;5}; I was willing to enter/display those two small ranges in the worksheet. Of course, the real goal was to allow arbitrary categories, arbitrary lengths, etc. Your sub-series data could be anything that fits that 2-column by n-subset-rows format; you might be adding horizontal or sloped lines or arbitrary values across part or all of the graph.
One way to do this is to create and graph an additional worksheet range “wsrng_FilledSubseries1Ys” of that same 26-row height, putting the matching Y values in their appropriate rows (“A”;”E”;”I”;”O”;”U”), and #N/A’s in the other rows (eg, Category Axis Tricks for Line and Area Charts - 1 - Peltier Tech Blog). Another way is a secondary X axis.
But I wanted to use the primary X axes and didn’t want to create extra tall columns in the worksheet for each of these extra subset series.
My plan was to create a defined name formula “defname_FilledSubseries1Ys” to return the desired tall array, and use that as the Y part of the subset-series definition in the chart. A number of approaches didn’t work because the chart seemed to “EVALUATE” the array formula and reduce it to the single first element of the array. Maybe others can get them to work, but for that reason I gave up on VLOOKUP, INDEX/MATCH, INDIRECT, and udf’s; array functions entered directly into and graphed from a worksheet range succeeded, but failed when EVALUATE-d into a worksheet range or displayed in the chart.
What eventually (!) worked requires only a single defined name formula for each subseries that you want to add to the graph:
defname_FilledSubseries1Ys, which refers to:
Code:
=N(OFFSET(wsrng_Subseries1Ys,MATCH(wsrng_AllXs,wsrng_Subseries1Xs,0) -1,0,1,1))The sub-series formula on the graph is then something like:
Code:
=SERIES("Subseries1XYs",Test.xlsx!wsrng_AllXs,Test.xlsx!defname_FilledSubseries1Ys,2)At one point I thought I had to wrap using TRANSPOSE(TRANSPOSE(N(OFFSET, but that doesn’t seem needed. Also, I’ve just started using it (Excel 2013 32-bit; Win7 64-bit), so there may still be surprises to come. But this was enough to get me on to using the solution to add the subseries I needed for the project. No biggie perhaps, but this doesn't require a macro-enabled worksheet.
Thad
