Complex Chart Series

[ToniShinobi]

I'd like to create a 3D chart, but the values for 3 different charts that i want to create are integrated together in the table.

The format of my Table is:

Column B contains a Location Name
Row 5 Contains Cells that are merged 3 at a time starting at Column C (so C5 is merged with D5 and E5, containing 12 AM, F,G,H5 contain 1 AM, so on until 11PM)
Row 6 From column C onwards contains individual titles for the below columns, 3 for each hour above (We'll say "Title1", "Title2", "Title3" are in cells C6, D6, E6, and continue in that pattern for each hour. F,G,H6 contain "Title1", "Title2", etc...).

I would like to make 3 charts.
Each series in a single chart will be of Title "Location" from B, and values C,F,I,L,... in the corresponding location. Notice the Values skip 2 in order to pick up only Values under the header "Title1".

The next chart will do the same thing, but with values from D,G,J,...
Last chart is the same with E,H,K,...

What can i do to automate this? Is this possible?

 

[gardnertoo]

Does your sheet look something like this? I've color coded the cells corresponding to the charts and series as I understand your needs; did I get it right?

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
5		12:00 AM			1:00 AM			2:00 AM			3:00 AM			4:00 AM
6	Location	Title1	Title2	Title3	Title1	Title2	Title3	Title1	Title2	Title3	Title1	Title2	Title3	Title1	Title2	Title3
7	Loc A	37.26206	3.178154	35.12741	11.55236	46.66164	95.89065	21.76207	97.45623	16.9198	59.38346	86.35587	92.99838	83.55004	50.36089	29.69909
8	Loc B	69.43952	52.06907	65.36996	4.448484	37.8171	11.79444	73.25215	42.34763	14.08063	0.391008	19.97814	20.26387	49.67236	96.05678	22.62912
9	Loc C	7.415993	47.61503	22.12621	69.20103	71.77445	12.00218	77.15735	51.72242	70.64127	3.582923	9.50843	42.45878	81.59129	0.688875	82.06296
10	Loc D	52.32108	20.07063	38.15302	7.044533	26.86232	33.31543	12.18688	60.07775	99.73725	94.46399	39.71081	69.89408	70.09842	2.964946	18.76839
11	Loc E	74.73847	20.54821	37.13956	78.05311	10.82185	92.13277	54.27729	43.84761	29.55916	59.07072	51.15142	81.10154	32.0379	61.31014	45.8482
12	Loc F	12.89574	40.68831	49.03521	9.702883	46.7204	42.92501	85.08624	91.0907	59.97911	26.84376	33.32311	90.3385	6.697468	41.15858	77.75763
13
14	Legend	Chart 1	Chart 1	Chart 3
15	Series 1
16	Series 2
17	Series 3
18	Series 4
Sheet1

 

[ToniShinobi]

Yes just like that... how do i create a chart for that?

 

[ToniShinobi]

Was a result ever found for this type of charting? The example shows exactly how my sheet looks.

 

[gardnertoo]

I think the easiest approach will be to create a range of cells somewhere else on the sheet, or even on another worksheet within the workbook, to extract the values you need for each chart. I'll post a sample once I work it up.

 

[gardnertoo]

Here's what I've got. I use the OFFSET function to "point" to the cell which corresponds to the time and Location needed for each series. I have room to post two of the groups, the third is similar:

ToniShinobi.xls
	B	C	D	E	F	G	H
5		12:00 AM			1:00 AM
6	Location	Title1	Title2	Title3	Title1	Title2	Title3
7	Loc A	37.26206	3.178154	35.12741	11.55236	46.66164	95.89065
8	Loc B	69.43952	52.06907	65.36996	4.448484	37.8171	11.79444
9	Loc C	7.415993	47.61503	22.12621	69.20103	71.77445	12.00218
10	Loc D	52.32108	20.07063	38.15302	7.044533	26.86232	33.31543
11	Loc E	74.73847	20.54821	37.13956	78.05311	10.82185	92.13277
12	Loc F	12.89574	40.68831	49.03521	9.702883	46.7204	42.92501
13
14	Title1	12:00 AM	1:00 AM	2:00 AM	3:00 AM	4:00 AM	5:00 AM
15	Loc A	37.26206	11.55236	21.76207	59.38346	83.55004	0
16	Loc B	69.43952	4.448484	73.25215	0.391008	49.67236	0
17	Loc C	7.415993	69.20103	77.15735	3.582923	81.59129	0
18	Loc D	52.32108	7.044533	12.18688	94.46399	70.09842	0
19
20	Title2	12:00 AM	1:00 AM	2:00 AM	3:00 AM	4:00 AM	5:00 AM
21	Loc A	3.178154	46.66164	97.45623	86.35587	50.36089	0
22	Loc B	52.06907	37.8171	42.34763	19.97814	96.05678	0
23	Loc C	47.61503	71.77445	51.72242	9.50843	0.688875	0
24	Loc D	20.07063	26.86232	60.07775	39.71081	2.964946	0
Sheet1

To get the formula for the third chart, create the same column headings and row headings, type Title3 in cell B26, copy the formula from cell C21 and paste it in cell C27, then change it as follows:
Before:

=OFFSET($B$6,MATCH($B21,$B$7:$B$12),MATCH($B$20,$C$6:$E$6)+3*(C$20-$C$20)*24)

After:

=OFFSET($B$6,MATCH($B27,$B$7:$B$12),MATCH($B$26,$C$6:$E$6)+3*(C$26-$C$26)*24)

After making these four changes (the three instances of 20 become 26, the one instance of 21 became 27), copy the formula down and across. These edits make the formula look to the group three headings instead of group 2.

Obviously, you'll be replacing "Title1" with the real title, etc.

 

[ToniShinobi]

Thank you.... I'm assuming that there isn't a way to get that to go directly into a chart instead of filling cells first for the chart. I separated all of the data into 3 tables manually for the chart. If it was possible to place functions in a chart as a series, i'm sure this offset function would have done wonders for my situation.

 

[gardnertoo]

Well, there is a way. A macro can be built that will step across the data grabbing every nth column and adding it to a chart. I've done it before when I was adding a large number of series to an xy scatter plot, each series had a different size range of x and y from the other series.