Search code examples
excelchartspivot-tableexcel-2013

Creating a multi-line chart in Excel - with calculations?

Dear StackOverflow Participants,

My post was ambiguous before, so here's a second attempt.

I have the following table of data:

Raw chart data

And hand-drawn examples of what I'd like to produce:

I'd like to produce a chart that displays calculated values for all codes (left-hand column of the Raw Chart Data pic) that have the same first four digits. I would like to display a data point for each date that sums all values under the "A" column. I would do the same with the "B" column. The "P" data point is problematic though, because it would use a calculation using each code that begins with the same first four digits. I'm referring to the "A", "B" and "P" that are under each date along the top of my spreadsheet as in the Raw Chart Data pic.

"P" is a % value, and has a corresponding "A" and "B" numeric value for a given, single code. I would multiply P with B for each single code with the same first four digits, and then sum each of those results. Then I would divide that sum into the sum of all the "B" values for those same individual codes.

I want to create a single 3-line chart for each unique group of codes that begin with the same four digits. As suggested in the comments, it makes sense to have the "P" values as a secondary axis, and use the primary axes for "A" and "B".

These codes will change, and new dates and associated values will be added every week, so I'm looking to have this chart be dynamic as the raw data gets added to/altered.

Is there any way to perform calculations like this for a chart in Excel? I think I'm familiar enough with VBA and Excel's formulae to do this, but I guess I'll find out.

Thank you All,

Angus

Solution

  • 2 sheet set-up.

    1) Sheet1 is called DataSheet and mirrors your data input sheet.

    2) Sheet2 is called ChartData and has both the transformed dataset for plotting your chart and the chart itself. This is how you actually want your dataset for charting. So if you can change the format of your reporting then always try and make a 'flat-file' table arrangement as shown.

    Here is what is looks like in the "after" state:

    2 sheets

    DataSheet sheet:

    Cell B2 in dataSheet you can input the 4 digit code to plot the chart for.

    Note the leading ' to preserve the 0 at the start.

    4 digit code selection

    Formulas in row 4:

    A) F4 for p:

    =IFERROR(SUMPRODUCT(OFFSET($A$6,0,SUM(COLUMN(H1)-1),COUNTA(Codes),1),--(LEFT(Codes,4)=$B$2),OFFSET($A$6,0,SUM(COLUMN(F1)-1),COUNTA(Codes),1))/H4,"")

    This applies the p calculation

    B) G4 for A:

    =SUMPRODUCT(OFFSET($A$6,0,SUM(COLUMN(G1)-1),COUNTA(Codes),1),--(LEFT(Codes,4)=$B$2))

    This sums, for the given 4 digit start code, column G.

    C) H4 for B:

    =SUMPRODUCT(OFFSET($A$6,0,SUM(COLUMN(H1)-1),COUNTA(Codes),1),--(LEFT(Codes,4)=$B$2))

    This sums, for the given 4 digit start code, column H.

    You can copy paste these in blocks of 3 to set the formulas for future ranges i.e. select F4:H4 and copy to I4:K4 etc and formulas will work for new range. Sorry, I haven't yet adjusted these so can drag in some way.

    Shout out to @Tom Sharpe and @shrivallabha.redij for solving the sumproduct riddle, here, which forms part of the solution.

    ChartData Sheet:

    A) Formula in B4:

    =DataSheet!F2

    This ensures that the start date is set to the first date in your range.

    B) Formula in B5 to drag down column B:

    =IF(ROWS($B$4:$B5) <=INT(COLUMNS(dates)/3),$B$4+ROWS($B$5:$B5)*7,NA())

    This ensures the date increments weekly (i.e. plus 7 days) for the required number of weeks. As each date is repeated 3 times, the number of columns in the dynamic range dates, which covers all the dates in the row 2 of dataSheet, is divided by 3 to get the number of weeks which should actually be plotted. If this number is exceeded #N/A is outputted as this will not be plotted on the chart.

    C) Formula in C4, which is dragged down column C, and across for as many columns as you are using i.e. to column E in the shown example:

     =IFNA(INDEX(SOquestions.xlsb!dataRange,MATCH($B4,dates,0)+COLUMNS($E:E)-1),NA())

    This retrieves the p, A and B values for the column B date. dataRange is the dynamic range which holds your source calculations in dataSheet. If you open the Name Manager, select dataRange, and then put your cursor inside the Refers To range, the dynamic range it references is outlined by "marching ants":

    dataRange

    If the date column (B) is #N/A in the chartData sheet then the associated p,A and B default to #N/A so are not plotted.

    Plotting the chart:

    The chart itself is a combination chart that has 4 dynamic series.

    ChartSeries

    The series are added in the normal way but you reference the dynamic series preceded by Sheetname! ; as in image. You use pSeries,aSeries, bSeries and dateSeries for the plot.

    The chart axis should be set up to handle weekly data points.

    X axis

    Combo-chart set-up:

    Combo chart

    Note p is plotted on the secondary x-axis and formatted as percentage.

    Process for updating:

    1) Add new rows in dataSheet

    Adding new rows

    2) Copy block of three columns to next column. Note that the first column of the block has a +7 to increment the week

    Copying formula and dates block

    3) Goto ChartData and drag formulas down in columns B:E

    chartDataSheet update

    4) Select the code you are interested in, in dataSheet cell B2 and everything updates.

    Named ranges required:

    Dynamic named ranges used. Ctrl & F3 will bring up Name Manager so you can add these.

    Named ranges

    Formulas for the dynamic named ranges:

    Codes

    =OFFSET(DataSheet!$A$6,0,0,COUNTA(DataSheet!$A$6:$A$1048576),1)

    dataRange

    =OFFSET(DataSheet!$F$4,0,0,1,COUNT(DataSheet!$F$2:$XFD$2))

    dates

    =OFFSET(DataSheet!$F$2,0,0,1,COUNT(DataSheet!$F$2:$XFD$2))

    pSeries

    =OFFSET(DataSheet!$P$11,0,0,COUNT(DataSheet!$B$11:$B$1048576),1)

    aSeries

    =OFFSET(ChartData!$D$4,0,0,COUNT(ChartData!$B$11:$B$1048576),1)

    bSeries

    =OFFSET(ChartData!$E$4,0,0,COUNT(ChartData!$B$11:$B$1048576),1)

    dateSeries:

    =OFFSET(ChartData!$B$4,0,0,COUNT(ChartData!$B$11:$B$1048576),1)

    Note: For earlier versions of Excel end row and columns have to be adjusted

    End Row 1048576 becomes 65536 End Column XFD becomes IV

    Further version info:

    http://www.excelfunctions.net/Excel-2003-vs-Excel-2007.html