Charting challenge

wiwchar

Board Regular
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

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Is there a way of posting a sample chart to show what I am after?

Anyone?

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.

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.

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.

Excellent! Thank you very much.

Replies
2
Views
387
Replies
2
Views
431
Replies
6
Views
281
Replies
5
Views
288
Replies
3
Views
360

1,219,029
Messages
6,145,869
Members
450,652
Latest member
JR1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back