Create an average of multiple excel chart without the data source

Question

Thought provoking problem (for me at least). Normally when creating a chart you have your data and then use it to create the chart. If you then copy the chart to another workbook, the values on the chart stay the same but there is "no available" data source in the new workbook. I want to create a new chart which is the average of multiple copied charts. Is this possible in excel/vba?

I can't even try recording a macro and going from there as I don't know if its possible to "average" multiple charts.

EDIT : Been doing some more thinking and am thinking if it is possible to instead of extract data into a new sheet for each chart, is it possible to average data upon extraction. If on the chart you Right click -> select data, you can see the reference to the data in the original worksheet. Is it possible to average this and print just the outcome without having to store all the data? Would still be easier to directly average charts if possible!

EDIT 2: I have reworked my data template so that matching time series data ranges is no longer an issue. Also as per the comment on averages-of-averages, the data is all of equal weight and quantity so this should not be a problem. It literally just comes down to: is there a way to take the face values of multiple charts (or graphs), and average them to form a new chart (or graph) without massive data manipulation in the original (or new) workbook?

Bounty Summary (with round numbers): Looking for a quick'ish way in VBA to create a chart which is the average of multiple charts. I have 10 types of chart on 50 separate worksheets. I'm looking to create a summary sheet with 10 charts that average the data from the same respective chart on the other 50 sheets. The key difficulty is that this is a 'presentation Workbook that all charts are copied into, all the data for each chart is in a different workbook.

EDIT 4: Data is stored in multiple time series tables that are all side by side in a main data sheet. It appears to be at the moment (as per Scott's comment) that there is no way to directly manipulate and the most likely solution will be that of data extraction/manipulation. Search still continues though :)

Answer 1

I want to create a new chart which is the average of multiple copied charts. Is this possible in excel/vba?

It is possible but there is no magic formula for this task.

I would first iterate each workbook, each worksheet, each shape and aggregate the values in an array, with one array for each type of chart. To avoid storing all the data, the averages will have to be computed upon each extraction like this:

Average = ((PreviousAverage * N) + Value) / (N + 1)

Next, to expose the data in your dashboard, I would duplicate the missing charts from the aggregated workbooks and reuse the one already present. This way, the customisation of the dashboard will remain untouched if all the charts are already there.

Finally, I would directly insert the aggregated values in the charts without storing them in a sheet.

I've assemble a working example that aggregates all the charts from the current workbook and displays the results in the sheet "Dashboard":

Sub AgregateCharts()

  Dim ws As Worksheet, wsDashboard As Worksheet, sh As Shape, ch As chart
  Dim xValues(), yValues(), yAverages(), weight&, key
  Dim items As Scripting.dictionary, item As Scripting.dictionary
  Set items = CreateObject("Scripting.Dictionary")

  ' define the dashboard sheet
  Set wsDashboard = ThisWorkbook.sheets("Dashboard")

  ' disable events
  Application.ScreenUpdating = False
  Application.EnableEvents = False

  ' iterate worksheets  '
  For Each ws In ThisWorkbook.Worksheets
    ' if not dashboard  '
    If Not ws Is wsDashboard Then
      ' iterate shapes      '
      For Each sh In ws.Shapes
        If sh.type = msoChart Then ' if type is chart    '

          Debug.Print "Agregate " & ws.name & "!" & sh.name

          ' check if that type of chart was previously handled
          If Not items.Exists(sh.chart.chartType) Then

            ' extract the values from the first serie
            xValues = sh.chart.SeriesCollection(1).xValues
            yValues = sh.chart.SeriesCollection(1).values

            ' duplicate the chart if it doesn't exists in the dashboard
            Set ch = FindChart(wsDashboard, sh.chart.chartType)
            If ch Is Nothing Then
              Set ch = DuplicateChart(sh.chart, wsDashboard)
            End If

            ' store the data in a new item   '
            Set item = New Scripting.dictionary
            item.Add "Chart", ch
            item.Add "Weight", 1   ' number of charts used to compute the averages
            item.Add "XValues", xValues
            item.Add "YAverages", yValues
            items.Add ch.chartType, item  ' add the item to the collection  '

          Else

            ' retreive the item for the type of chart  '
            Set item = items(sh.chart.chartType)
            weight = item("Weight")
            yAverages = item("YAverages")

            ' update the averages : ((previous * count) + value) / (count + 1)  '
            yValues = sh.chart.SeriesCollection(1).values
            UpdateAverages yAverages, weight, yValues

            ' save the results  '
            item("YAverages") = yAverages
            item("Weight") = weight + 1

          End If

        End If
      Next
    End If
  Next

  ' Fill the data for each chart in the dashboard
  For Each key In items
    Set item = items(key)
    Set ch = item("Chart")

    ' Add the computed averages to the chart
    ch.SeriesCollection(1).xValues = "={" & Join(item("XValues"), ";") & "}"
    ch.SeriesCollection(1).values = "={" & Join(item("YAverages"), ";") & "}"
  Next

  ' restore events
  Application.EnableEvents = True
  Application.ScreenUpdating = True

End Sub

Private Sub UpdateAverages(averages(), weight&, values())
  Dim i&
  For i = LBound(averages) To UBound(averages)
    averages(i) = (averages(i) * weight + values(i)) / (weight + 1)
  Next
End Sub

Private Function DuplicateChart(ByVal source As chart, target As Worksheet) As chart

  ' clone the chart to the target
  source.Parent.Copy
  target.Paste
  Application.CutCopyMode = 0

  ' clear the data '
  With target.Shapes(target.Shapes.count).chart.SeriesCollection(1)
    Set DuplicateChart = .Parent.Parent
    .name = CStr(.name)
    .xValues = "={0}"
    .values = "={0}"
  End With

End Function

Private Function FindChart(source As Worksheet, chartType As XlChartType) As chart

  ' iterate each shape in the worksheet to fin the corresponding type
  Dim sh As Shape
  For Each sh In source.Shapes
    If sh.type = msoChart Then
      If sh.chart.chartType = chartType Then
        Set FindChart = sh.chart
        Exit Function
      End If
    End If
  Next

End Function

Answer 2

Some data manipulation will probably be necessary. However, you can do it all in memory (or in a hidden worksheet if you prefer).

To extract data from a chart, example code:

Sub chartTest()
    Dim ch As ChartObject
    Set ch = Worksheets(1).ChartObjects(1)
    Dim nr As Variant, var As Variant, var 2 As Variant

    nr = UBound(ch.Chart.SeriesCollection(1).Values)

    ' Paste the values back onto the sheet
    Range(Cells(1, 1), Cells(nr, 1)) = Application.Transpose(ch.Chart.SeriesCollection(1).XValues)
    Range(Cells(1, 2), Cells(nr, 2)) = Application.Transpose(ch.Chart.SeriesCollection(1).Values)

    ' Pull the values into a variable (will be in array format)
    var = ch.Chart.SeriesCollection(1).XValues
    var2 = ch.Chart.SeriesCollection(1).Values

    ' Retrieval example
    For i = 1 To UBound(var)
        Range("A" & i).Value = var(i)
        Range("B" & i).Value = var2(i)
    Next i
End Sub

Whether you use Chart or ChartObjects as a first stop seems to depend on how the chart is created. The code in this example worked for a chart created by right-clicking some data in a sheet and inserting the chart.

See the Chart.SeriesCollection and the Series Properties pages on MSDN for more information.

So basically, extract all the data from the charts using code similar to the above, compare them, and create a new chart based on this data.