Charting challenge

wiwchar

Board Regular
Joined
Sep 11, 2003
Messages
166
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
ABCDEFGHIJ
1DATETARGETACTUALMONTHPRODUCTPRODUCT(All)
25-Jan-055057JanB
37-Jan-053231JanEData
420-Jan-055051JanBMONTHAverage of ACTUALAverage of TARGET
522-Jan-054340JanAJan4544
64-Feb-0510097FebCFeb6761
74-Feb-053255FebEMar7168
89-Feb-055048FebBGrand Total6057
915-Mar-058080MarD
1016-Mar-054349MarA
1122-Mar-05100101MarC
1223-Mar-055055MarB
Sheet1
Book3
ABCD
1MonthActualTarget
2Jan4043
3Feb43
4Mar4943
5Apr43
6May43
7Jun43
8Jul43
9Aug43
10Sep43
11Oct43
12Nov43
13Dec43
14YTD4543
Sheet1
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,931
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Sep 11, 2003
Messages
166

ADVERTISEMENT

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

MrExcel MVP
Joined
May 14, 2003
Messages
4,931
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
In L1:N14 I inserted this range:

Code:
      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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,635
Members
412,334
Latest member
ExcelForLifeDontHate
Top