Recently I've had to create a series of Bar charts that incorporate points that allow additional data labels. I've created these points by building in an XY scatter chart into the Bar chart. The additional data labels are tied to the XY points using a tool such as John Walkenbach's J-Walk Chart Tools or Rob Bovey's XY Chart Labeler.

An example of one of these charts, less data labels, looks like this:

The person who requested the chart wanted each XY point to be visible as well as vertically centered within each bar. To do so, I started by setting the gap width to 100%. Next, a calculation creates the Y values of the XY points as illustrated below.

The calculation starts with the source data for the bar chart which is contained in the range B4:E8. There are four categories and three series within each category. I've entered those numbers into the yellow highlighted cells D19 and D20. The calculation of the lowest Y point on the XY chart vertical axis is contained in cell D23. The calculation is:

=D20/(D20+1)

Once this calculation is done, the results can be applied to all of the Y values of the XY chart. The XY chart source is contained in the range C11:H15. The results of the calculation above are linked to cell D12. Cell D13 adds the series per category value to the results in C12. The formula in D13 is:

=D12+$D$20

Cell F12 adds the series per category value to the results in C12. The formula in F12 is:

=D12+$D$23

Finally, the maximum value of the secondary axis is simply the number of categories multiplied by the series per category. The formula in cell D24 is:

=D19*D20

The VBA procedure below attempts to quickly build this chart based on the template above. Please note that it references the color palette that I currently use - your colors may be different. At this point the output produces the following:

Given time the procedure could be written to complete the formatting. It could also be modified to automatically accept a reasonable number of changing categories and series, use a custom color palette, and allow more efficient range references.

Option Explicit

Sub BuildChart()

    Application.ScreenUpdating = False

    Dim XVals As Range
    Dim Srs1 As String
    Dim Srs2 As String
    Dim Srs3 As String

    Set XVals = ActiveSheet.Range("B5:B8")
    Srs1 = ActiveSheet.Range("C4").Value
    Srs2 = ActiveSheet.Range("D4").Value
    Srs3 = ActiveSheet.Range("E4").Value

    Charts.Add
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

    With ActiveChart.SeriesCollection.NewSeries
        .Name = Srs1
        .ChartType = xlBarClustered
        .XValues = XVals
        .Values = Sheets("Sheet1").Range("C5:C8")
        .Interior.ColorIndex = 22
        .Border.LineStyle = xlNone
    End With

    With ActiveChart.SeriesCollection.NewSeries
        .Name = Srs2
        .ChartType = xlBarClustered
        .XValues = XVals
        .Values = Sheets("Sheet1").Range("D5:D8")
        .Interior.ColorIndex = 35
        .Border.LineStyle = xlNone
    End With

    With ActiveChart.SeriesCollection.NewSeries
        .Name = Srs3
        .ChartType = xlBarClustered
        .XValues = XVals
        .Values = Sheets("Sheet1").Range("E5:E8")
        .Interior.ColorIndex = 24
        .Border.LineStyle = xlNone
    End With

    With ActiveChart.SeriesCollection.NewSeries
        .Name = Srs1 & "_XY"
        .ChartType = xlXYScatter
        .XValues = Sheets("Sheet1").Range("C12:C15")
        .Values = Sheets("Sheet1").Range("D12:D15")
        .MarkerSize = 3
        .MarkerStyle = xlDiamond
        .MarkerBackgroundColorIndex = 3
        .MarkerForegroundColorIndex = 3
    End With

    With ActiveChart.SeriesCollection.NewSeries
        .Name = Srs2 & "_XY"
        .ChartType = xlXYScatter
        .XValues = Sheets("Sheet1").Range("E12:E15")
        .Values = Sheets("Sheet1").Range("F12:F15")
        .MarkerSize = 3
        .MarkerStyle = xlDiamond
        .MarkerBackgroundColorIndex = 10
        .MarkerForegroundColorIndex = 10
    End With

    With ActiveChart.SeriesCollection.NewSeries
        .Name = Srs3 & "_XY"
        .ChartType = xlXYScatter
        .XValues = Sheets("Sheet1").Range("G12:G15")
        .Values = Sheets("Sheet1").Range("H12:H15")
        .MarkerSize = 3
        .MarkerStyle = xlDiamond
        .MarkerBackgroundColorIndex = 5
        .MarkerForegroundColorIndex = 5
    End With

    ActiveChart.ChartGroups(1).GapWidth = 100

    ActiveChart.Axes(xlCategory, xlSecondary).MaximumScale = ActiveSheet.Range("D25").Value
    ActiveChart.Axes(xlCategory, xlSecondary).MinimumScale = 0
    ActiveChart.Axes(xlCategory, xlSecondary).MajorUnit = 1

    ActiveChart.Axes(xlValue).MaximumScale = ActiveSheet.Range("D25").Value
    ActiveChart.Axes(xlValue).MinimumScale = 0
    ActiveChart.Axes(xlValue).MajorUnit = 1

    ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = ActiveSheet.Range("D24").Value
    ActiveChart.Axes(xlValue, xlSecondary).TickLabels.NumberFormat = "#,##0"

    ActiveChart.Parent.Height = 189.75
    ActiveChart.Parent.Width = 319.5

End Sub

Recently the topic of primary and secondary axis scales was discussed at the PTS Blog. The discussion starts with a reference to Stephen Few's March 2008 Visual Business Intelligence Newsletter titled Dual-Scaled Axis in Graphs - Are They Ever the Best Solution . Stephen starts his article by concluding that He then goes on to cite a series of examples. Finally, at the end of his article he states that "I certainly cannot conclude, once and for all, that graphs with dual-scaled axes are never useful; only that I cannot think of a situation that warrants them in light of other, better solutions. I invite you to propose viable exceptions, which I will welcome with open arms."

From a healthcare finance perspecitive, it has been my experience that these charts are great tools for those who know how to build them and for those audiences that know how to interpret the underlying data. Below are several comments I have concerning Stephen's article:

Comment #1 - It's not necessarily the chart with two axes that's bad, it's the data behind it. In the newsletter, most of Stephen's graphs illustrate the relationship between revenue and units sold. I would argue that is comparison is flawed from the beginning and, as such, this type of chart should not be used. My reasoning is that, although it seems like the data is should be related, when you drill down into the data you might find out that the correlation is not as close as you might have thought. Depending on the data:

• What defines a unit?
• Are the units the same unit each quarter or are there multiple types of units being charted as one?
• If there are multiple units, how are the changes in volume accounted for within the sales mix?
• Won't price increases distort the data over time?
• How does the chart account for decreases in purchasing power over time i.e inflation over time?
• If the data is measured by quarter and one of the quarters includes a leap year, how do you account for the extra day?

In my opinion, I think an argument can be made that a dual axis chart showing revenue and units sold is probably not the the best use of a dual-axes chart. That being said I do have to admit that I do use this type comparison at a top-level where the components of variance might be considered immaterial. If a dual-axes chart is to be used the audience should be educated on how to interpret the underlying data and how to recognize and address possible flaws before decisions a made.

Comment #2 - If you are going to present a dual-axis chart, the axes need to be proportional. That means doing the math for each axes to make sure it is in fact proportional to the other.

Comment #3 - As Stephen observed, line graphs are the best presentation for this type of chart.

Comment #4 - "I can’t think of a single case when there isn’t a better solution than a graph with a dual-scaled axis." In my line of work, we've found that these graphs are very useful for auditing calculations. For example, you are tasked with building a projection of revenue and discounts for the next year. In a healthcare environment, revenue and discounts both contain the same components of variance i.e. work days, fee increases, volume changes, payer mix changes, and changes in service mix or acuity. The calculations the build all of these components can get very complex. Rather than attemping to proof each calculation that builds the projection, this chart can quickly pick up areas of possible error.

In the example below, it's obvious that there's problem with June calculations because the lines are not in sync for that month.

Comparing the growth in same-type volumes (for example Radiology vs. Laboratory volumes) is another example.

To conclude, there may in fact be better charts for displaying certain types of data. But if the audience understands the relationships between the data I think there is a place for these charts. And like the example above shows, there's most definitely a place for these charts when performing audits.

Gross revenue can be simply defined as charges billed. Net revenue, on the other hand, is more reflective of the revenue you expect to actually receive. In a healthcare environment, the spread between gross and net revenue can be fairly significant depending on who pays the bill.

A bar chart provides a simple visual comparison of gross vs. net revenue. Although the stacked bar chart initially seemed like the best choice to me, over time I've found that a regular bar chart with the bar overlap set to 100% works well too.

In this example, the goal is to chart the change in gross and net revenue in the Commercial / Contract and Government areas for the annual periods 2006 and 2007. Below is how I've set up the data:

The Chart Wizard produces the following:

The next task is to overlay the bars by setting the overlap to 100%. To do so, go to the Format Data Series dialog box - Options tab and change the overlap to 100%.

Finally, with a series of minor formatting changes the chart looks like this:

The chart shows the following:

• The Government area is above the blue line and the Commercial / Contract area is below the blue line.
• The Commercial / Contract revenue grew while the Government gross revenue showed no increase and reimbursement decreased.
• The Commerical / Contract reimbursement is significantly higher than the Government reimbursement. As a result, the Commercial / Contract area looks much more attractive from a financial perspective.

Pie charts, as well as many other charts, can be built to show percentages of a whole. Unfortunately, many times the pie chart percentages do not tie in total to the total of the source document percentages. Microsoft acknowledges the problem via this knowledge base article titled Pie Chart Shows Incorrect Percentage Value in Excel.

As a potential work-around, I attempted to create a source document formula that works similar to the logic described in the knowledge base article. The purpose of the formula is to force the rounded percentages in the source report to always tie to the pie chart percentages.

Refer to the screenshot below:

A source range, named "Data", covers the range C4:C10. Column D reflects the rounded source document percentages. When added together, the percentages equal 101%.

Column E contains the alternative percentages that tie to the pie chart. The formula, entered as a multicell-array into the range E4:E10, is as follows:

{=IF((Data-ROW()/10^10)=LARGE((Data-ROW()/10^10),1),
1-SUMPRODUCT(((Data-ROW()/10^10)<>LARGE((Data-ROW()/10^10),1))
*(ROUND(Data/$C$11,2))),ROUND(Data/$C$11,2))}

The formula works essentially as the knowledge base article describes. It:

• Uses the ROW function to first break any ties that may occur when the same number appears two or more times.
• Using the numbers calculated by the ROW function, it then uses the LARGE function to determain which percentage is the maximum.
• If the percentage is the maximum, it subtracts the total of all of the the percentages excluding the maximum from 100%. All other percentages are calculated and rounded as normal.

In the example, the formula recognizes the rounded value for Gamma as the maximum. Because the data does not add to 100%, the formula subtracts one from the Gamma value to force the percentages to round to 100%. Likewise, the percentages in the pie chart show the same values.

To test the formula, I used the RANDBETWEEN function and a macro to quickly test 10,000 random scenarios. The formula appeared to work well with values that round in total to 99% or 101%. However, when values round to 98% or 102%, I did find occurances where the formula returns values that differ from the pie chart. In those cases Excel does not seem to assign the difference to one single value. Rather, Excel appears to pick two of the same values and distribute the difference evenly. Given the logic as described in the knowledge base article, I'm at a loss to explain why.

Below is an example where the pie chart percentages differ from the formula:

The formula calculates Beta to be the maximum and subtracts two from it to force the total to equal 100%. However, Excel subtracts one from Beta and Delta to get to 100%.

Jon Peltier provides an excellent example of how to create waterfall charts that cross the x axis on his Peltier Technical Services site. These charts are especially useful for illustrating the components of variance.

Adapting Jon's waterfall charting examples to show the components of variance in a healthcare environment (for Hospitals, Clinics, etc.) requires an extra step. Normally, a positive variance would be shown with a green bar moving up and a negative variance would be shown with a red bar moving down. In a healthcare setting there's an extra twist. A positive variance can be shown with a green bar moving up or down. Likewise, a negative variance can be shown with a red bar moving up or down. Given these additional requirements, the formulas that drive the variance bars must be modified to reflect these types of changes.

To further explain, revenue provided by commercial and contract payers allows a greater reimbursement than revenue provided by government payers. As a result, an increase in the revenue provided by a commerical and contract payer would be favorable. A decrease in the revenue provided by a government payer is also favorable. Conversely, a decrease in revenue provided by commercial and contract payers may be viewed as unfavorable while an increase in revenue provided by government payers would also be viewed as unfavorable.

Below is an example of how a healthcare waterfall chart might look:

To allow for the changes in color if the variance is positive or negative, I've added a "Yes" or "No" color switch to the range C29:C33. Rather than the values determining if the color of the bar is positive or negative, the Yes-No switch allows the user to determine whether a change in a bar is positive or negative.

The example shows an overall decrease in volume. However, although the decrease in volume is normally viewed as negative, the reimbursement associated with the decrease may actually increase. The increase in reimbursement is a result of the positive commerical and contract variance combined with a negative government variance. To summarize, even though the volumes are decreasing the net revenue per unit of volume increases which allows for the possibility of greater profitability.

Since the example is somewhat complex, rather than explaining how each of the formulas work I've provided a link to the example file below.

Recently a project required that I perform calculations on the point values of an Excel chart using VBA. Upon researching how to do so, I found that writing the code is not as easy as I originally thought. The task becomes difficult because the point object does not have a values property.

Fortunately I found a solution that was posted by Andy Pope in response to a question posted to the Ozgrid.com forum. Below is Andy's example:

x=activechart.SeriesCollection(1).values 
For i=LBound(x) To UBound(x) 
    Debug.Print "Point ";i;"=";x(i) 
Next i 

In the solution above, "x" is a single-dimension array that hold the point values of "SeriesCollection(1)". A loop is used to increment through the array and return the point values.

Using this solution as a start, I needed to expand it to cover all of the series in a chart. The example below is the result:

Option Explicit

Sub PointValues1()

   Dim arrValues() As Variant
   Dim Srs() As Variant
   Dim Cnt As Integer
   Dim Sr As Integer
   Dim Pt As Integer
   Dim i As Integer
     
   Cnt = 0

    For Sr = 1 To ActiveChart.SeriesCollection.Count
        Srs = ActiveChart.SeriesCollection(Sr).Values
        For Pt = 1 To ActiveChart.SeriesCollection(Sr).Points.Count
             Cnt = Cnt + 1
             ReDim Preserve arrValues(1 To Cnt)
             arrValues(Cnt) = Srs(Pt)
        Next Pt
    Next Sr

    For i = 1 To UBound(arrValues)
        Debug.Print arrValues(i)
    Next i

 End Sub

In PointValues1, the first loop is used to increment through each series on the chart. Like Andy's example, the statement

Srs = ActiveChart.SeriesCollection(Sr).Values

is used to extract the point values from the active series. Because the array needs to hold all of the point values of the chart, a simple counter is used to record the placement of each point value within the array where that value should fall.

I created the following chart in which to test the macro above:

The macro returns the following point values in the immediate window from the "arrValues" array:

Next, I modified the macro above to perform several calculations using the point values stored in the array. The modified code below returns a maximum, minimum, total, and average value for all of the data points in the chart:

Option Explicit

Sub PointValues2()

   Dim arrValues() As Variant
   Dim Srs() As Variant
   Dim Cnt As Integer
   Dim Sr As Integer
   Dim Pt As Integer
   Dim i As Integer
   Dim MaxVal As Double
   Dim MinVal As Double
   Dim TotVal As Double
   
   Cnt = 0

    For Sr = 1 To ActiveChart.SeriesCollection.Count
        Srs = ActiveChart.SeriesCollection(Sr).Values
        For Pt = 1 To ActiveChart.SeriesCollection(Sr).Points.Count
             Cnt = Cnt + 1
             ReDim Preserve arrValues(1 To Cnt)
             arrValues(Cnt) = Srs(Pt)
        Next Pt
    Next Sr

    MaxVal = arrValues(1)
    MinVal = arrValues(1)
    
    For i = 1 To UBound(arrValues)
        If arrValues(i) > MaxVal Then MaxVal = arrValues(i)
        If arrValues(i) < MinVal Then MinVal = arrValues(i)
        TotVal = arrValues(i) + TotVal
    Next i

 Debug.Print "Maximum Value = " & MaxVal
 Debug.Print "Minimum Value = " & MinVal
 Debug.Print "Range Total = " & TotVal
 Debug.Print "Range Average = " & TotVal / UBound(arrValues)

End Sub

The macro returns the following point values in the immediate window from the "arrValues" array:

Finally, I thought to experiment with creating a two-dimension array to store the point values and a one-dimension array to store the values from the X-Axis. In this example, a counter like those used in examples 1 and 2 is replaced with two loops. The outer loop is based on the number of series on the chart and the inner loop is based on the number of points within each series. The code below is the result:

Option Explicit

Sub PointValues3()

    Dim arrValues() As Variant
    Dim Srs() As Variant
    Dim xVal() As Variant
    Dim SrCnt As Variant
    Dim PtCnt As Variant
    Dim Sr As Integer
    Dim Pt As Integer
    Dim i As Integer
    Dim j As Integer
    
    SrCnt = ActiveChart.SeriesCollection.Count
    PtCnt = ActiveChart.SeriesCollection(1).Points.Count
        
    For Sr = 1 To ActiveChart.SeriesCollection.Count
        Srs = ActiveChart.SeriesCollection(Sr).Values
        xVal = ActiveChart.SeriesCollection(Sr).xValues
        For Pt = 1 To ActiveChart.SeriesCollection(Sr).Points.Count
            ReDim Preserve arrValues(1 To SrCnt, 1 To PtCnt)
            arrValues(Sr, Pt) = Srs(Pt)
        Next Pt
    Next Sr

    For i = 1 To SrCnt
        For j = 1 To PtCnt
            Debug.Print "Series " & i & " " & "Point " & xVal(j) & " Value: " & arrValues(i, j)
        Next j
    Next i

End Sub

The macro returns the following point values in the immediate window from the "arrValues" array: