Charting challenge

[wiwchar]

Every month I need to create charts that update the year-to-date information on a number of products. I have done this by using a pivot table and then manually creating a table of data for each product for a line-column chart. Can this be done more efficiently?

The chart needs all the months along the bottom as well as a YTD (year-to-date) value. The target is a line and the actuals are the columns.

Examples of the data with pivot and the manually created data for product A are below.

Book2
	A	B	C	D	E	F	G	H	I	J
1	DATE	TARGET	ACTUAL	MONTH	PRODUCT			PRODUCT	(All)
2	5-Jan-05	50	57	Jan	B
3	7-Jan-05	32	31	Jan	E				Data
4	20-Jan-05	50	51	Jan	B			MONTH	Average of ACTUAL	Average of TARGET
5	22-Jan-05	43	40	Jan	A			Jan	45	44
6	4-Feb-05	100	97	Feb	C			Feb	67	61
7	4-Feb-05	32	55	Feb	E			Mar	71	68
8	9-Feb-05	50	48	Feb	B			Grand Total	60	57
9	15-Mar-05	80	80	Mar	D
10	16-Mar-05	43	49	Mar	A
11	22-Mar-05	100	101	Mar	C
12	23-Mar-05	50	55	Mar	B
Sheet1

Book3
	A	B	C	D
1	Month	Actual	Target
2	Jan	40	43
3	Feb		43
4	Mar	49	43
5	Apr		43
6	May		43
7	Jun		43
8	Jul		43
9	Aug		43
10	Sep		43
11	Oct		43
12	Nov		43
13	Dec		43
14	YTD	45	43
Sheet1

 

[wiwchar]

Is there a way of posting a sample chart to show what I am after?

 

[wiwchar]

Anyone?

 

[Jon Peltier]

Describe in words what the chart should look like, and try to show more clearly what the data looks like. I tried to answer this yesterday, but the two screens you included didn't help me figure out what you started with or exactly how to make it look.

 

[wiwchar]

Book 2 is the beginning. In Book 2, columns A - E are the data that is obtained. The pivot table in columns H - J is what I have been using to separate the data by product and month. For each product and every month, I obtain the average of the ACTUAL score and the average of the TARGET score. Using the pivot table, I create Book 3 from which I generate the chart. Right now, I have been keying in the data for each product. Some products do not have ACTUAL data in some months, therefore blank. Book 3 is an example of what I would get by keying the data manually from the pivot table in Book 2 for Product A.

The chart would have the 12 months of the year plus YTD along the x-axis. The columns on this chart would be the ACTUAL scores for the product in the particular month. The chart generated from the example in Book 3 would have a column for Jan, Mar and YTD. A line for TARGET would extend from Jan right through to and including YTD.

Thanks.

 

[Jon Peltier]

In L1:N14 I inserted this range:

      Actual   Target
Jan   44.750   43.750
Feb   66.667   60.667
Mar   71.250   68.250
Apr    #N/A     #N/A
May    #N/A     #N/A
Jun    #N/A     #N/A
Jul    #N/A     #N/A
Aug    #N/A     #N/A
Sep    #N/A     #N/A
Oct    #N/A     #N/A
Nov    #N/A     #N/A
Dec    #N/A     #N/A
YTD   60.364   57.273

Jan to Dec and YTD, plus Actual and Target, are labels, cell L1 is blank. Cell M2 has this formula, which is filled to N13:

=INDEX($H$4:$J$17,MATCH($L2,$H$4:$H$17,0),MATCH("average of "&M$1,$H$4:$J$4,0))

Cell M14 has this formula:

=INDEX($H$4:$J$17,MATCH("Grand Total",$H$4:$H$17,0),MATCH("average of "&M$1,$H$4:$J$4,0))

which is filled across to N14. The #N/A means there was mo matching month, but that's okay, in XY and Line charts, #N/A is your friend. When the pivot table is updated, the lookups will have more table to look in.

Make a line chart with this data. Where you have #N/A, the line will simply interpolate past the #N/A to the next existing value.

 

[wiwchar]

Excellent! Thank you very much.