As a follow-up to Align Charts to Worksheet Grid Post 1, the following procedure assigns a name, sizes, and aligns all of the embedded charts on a single worksheet. The key to the procedure is the location (range address) of the cell at the top left side of the chart.

Sub AlignCharts()

    Dim Cht As ChartObject

    For Each Cht In ActiveSheet.ChartObjects
        'Note: Naming the chart is optional.
        Cht.Parent.Name = Cht.TopLeftCell.Offset(-1, 0).Value
        Cht.Top = Cht.TopLeftCell.Top
        Cht.Left = Cht.TopLeftCell.Left
        Cht.Height = 114.75
        Cht.Width = 192
    Next Cht

End Sub

Given four embedded charts on a single sheet, the output looks like this:

Each chart is assigned the name of the value in the cell just above the top left cell. Each chart is aligned to the top-left cell and sized as per the height and width assignments.

With this procedure you don't have to worry about naming the charts first or building loops to place them. Just create a new chart, move it to the top-left cell location of your choice, add a name for the chart above the top-left cell, and run the macro.

In Excel there is no way to automatically attach text labels to data points within a chart. However, Microsoft has provided a VBA procedure to do so via it's Knowledge Base Article 213750.

The macro can be shortened to the following. To run the macro, first copy it to a standard module. Next, activate the chart and run it.

Sub AddLabels()
    Application.ScreenUpdating = False
    Dim Rng As Range
    Dim Cht As Chart
    Dim i As Integer
    Set Cht = ActiveChart
    Set Rng = ActiveSheet.Range("A1:A10")
    Cht.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowLabel
    Pts = Cht.SeriesCollection(1).Points.Count
    For i = 1 To Pts
        Cht.SeriesCollection(1).Points(i).DataLabel.Text = Rng(i)
    Next i
    Application.ScreenUpdating = True
End Sub

To add the functionality of automatically updating the data labels whenever the cells are changed, the macro can be slightly modified to the following:

Replace

Cht.SeriesCollection(1).Points(i).DataLabel.Text = Rng(i)

with

Cht.SeriesCollection(1).Points(i).DataLabel.Text = "=" & Rng(i).Address(, , xlR1C1, True)

Finally, if you don't feel like fooling around with code, there are two free utilities available that allow you to attach text labels to data points within a chart:

• John Walkenbach's J-Walk Chart Tools
• Rob Bovey's XY Chart Labeler

A recent post to the Microsoft Excel Charting newsgroup asks "I have a chart where two lines intersect at the breakeven point. How can I show a dynamic label at the point of intersection that describes the breakeven point?"

A simple solution is to add a text box in the chart. Make the source of that text box a worksheet cell that contains the data that you want to show.

For example, the model below provides the data needed to create a breakeven chart.

Based on the information above, a combination Line - XY Scatter Chart can be created to show the breakeven point.

When changes are made to the model, the label at the top of the chart will always reflect those changes. To create the label, first decide what worksheet cell you want the label to tie to. In this case the label ties to cell C25. Within that cell you can reference what is already there or create a phase that you want to appear. In this example the phrase "BE Units = 152.16 and BE Cost = $4,868" is returned by using the following formula in cell C25:

="BE Units = "&TEXT(F5,"##.##")&" and "&"BE Cost = "&TEXT(G5,"$#,###")

Finally, add the label to the chart. To do so, select the chart (select any element except a text item). Click in the formula bar, then type the number if it's static or type = and click on the cell with the value (no need to create the textbox first). Press enter and a textbox should appear in the middle of the chart. This will put a textbox in the chart.

Note: Instructions for adding the text box to the chart was provided by Jon Peltier of Peltier Technical Services via a past post to the Microsoft Excel Charting Newsgroup.

The following example uses a combination of defined names and array formulas to dynamically chart a range between a high and low value. In the screenshot below, data is entered into the yellow shaded cells. The chart source is within the white grid range E3:F22.

The first step to creating the chart is to build four named ranges. Three of the four named ranges will act as components of the array formulas that make up the chart source. Each of the formulas can be viewed in the screenshots of the named range dialog boxes below:

The first named range is called "Data":

The second named range is called "Labels":

The third named range is called "List":

The fourth and final named range is called "Values":

Two array formulas make up the chart source. To enter an array formula, select the entire range, enter the formula into the formula bar, and then hit the CONTROL - SHIFT - ENTER keys at the same time.

The first array formula below covers the range E3:E22.

=IF(ISERR(SMALL(IF(Data=F3,ROW(Data)-MIN(ROW(Data))+1),COUNTIF($F$3:F3,F3)))," ",
INDEX(List,SMALL(IF(Data=F3,ROW(Data)-MIN(ROW(Data))+1),COUNTIF($F$3:F3,F3))))

The second array formula below covers the range F3:F22.

=IF(ISERR(SMALL(IF(Data>=$H$3,IF(Data<=$H$2,ROW(INDIRECT("1:"&ROWS(Data))))),
ROW(INDIRECT("1:"&ROWS(Data))))),"",INDEX(Data,SMALL(IF(Data>=$H$3,
IF(Data<=$H$2,ROW(INDIRECT("1:"&ROWS(Data))))),ROW(INDIRECT("1:"&ROWS(Data))))))

Finally, it's time to create the chart. Assuming that the file name is "RangeBetween.xls", the chart source dialog box looks like this:

In Components of Healthcare Variances, five high-level components of revenue variance were identified. Those five components are fees, work days, pure volume, payer mix, and acuity. In Components of Healthcare Variances Part 2, it was shown that three of the five high-level components of revenue variance are contingent on volume. Of the three volume components, only the work day component is readily identifiable. This post will present a method for calculating the pure volume and payer mix components.

Before presenting the calculation it's important to understand the current state of Healthcare today. At a very high level and from a financial perspective there are only two types of patients. The first type of patient can be called a "Nominal" patient. Patients in the Nominal group generally carry insurance or pay cash for services performed by the healthcare provider. Each year healthcare providers can pass increases in their costs to the Nominal patients by raising fees. The cost side includes salaries and benefits for the healthcare provider's employees, general inflation, supply and drug costs, building costs, etc. The second type of patient can be called a "Government" patient. Government patients generally carry Medicare or other government provided healthcare plans. The problem with the Government group is that healthcare providers historically have not been able to pass their costs on to this group. Fee increases for Medicare must be approved annually by Congress. Historically the increases that are approved do not keep up with general inflation let alone increases in salaries for physicians, nurses, pharmacists, and other technical staff, general supply and drug costs, etc. As a result, many healthcare providers are trying to shift their practices away from lower reimbursing patients like the Medicare group to higher reimbursing Nominal patients just to stay in business. Shifts from Nominal to Government or Government to Nominal patients are refered to as changes in payer mix.

In the example below, the total volumes of procedures performed and workdays for the Nominal and Government payer groups are given. Those numbers are input into the yellow shaded cells.

The first step to calculating the pure volume and change in payer mix is to calculate the total volumes and the change in total volumes. The total volume can be calculated by simply adding together the Nominal and Government volumes. The change is simply the percentage difference in the two years i.e. 2007 vs. 2006. Those calculations are highlighted in green below.

Step 2 is to identify the "pure volume" change. Pure volume is the change in volume exclusive of any change in work days. In the example above, total volumes decrease by 0.3%. However, that change includes an increase of 0.4% in work days. As a result, the pure volume change must be something less than -0.3%. The cells shaded green below show the components of the calculation of pure volume.

The formula in cells H12 and K12 is:

=($N$8-$N$10)/(1+$N$10)

The calculation can be proven by the following:

Pure Volume Change + Work Day Change + (Pure Volume Change * Work Day Change) = Total Volume Change 

or

-0.7% + 0.4% + (-0.7% * 0.4%) = -0.3%

Step 3 is to calculate the payer mix percentages in 2006 and 2007 for each payer group. Using the Nominal group for 2007 as the first example, the components of the payer mix calculation as shown below in green:

The formula in cell F14 is:

=F8/$L8

Continuing step 3, use the Government group as an example. Again, the components of the payer mix calculation as shown below in green:

The formula in cell I14 is:

=I8/$L8

Moving on to step 4, calculate the percentage change in payer mix percentages. The components of these calculations are shown in green below:

The formula in cell H14 is:

=IF(ISERROR((F14-G14)/ABS(G14)),0,(F14-G14)/ABS(G14))

The formula in cell K14 is:

=IF(ISERROR((I14-J14)/ABS(J14)),0,(I14-J14)/ABS(J14))

Finally, step 5 computes the components of volume associated with each of the three areas of variance within each payer group. In other words, we can now calculate and assign volumes to the pure volume, work day, and payer mix components of variance within the Nominal and Medicare groups. In the screenshot below, match the colors in the variance analysis range F19:G25 against the percent changes above. The volumes associated with each component of change is a simple multiplication on the individual component of change.

Note how the change in payer mix (shown in row 24 - cells shaded green) nets to zero. By definition a change in mix only shifts volumes between groups. The net change of zero is exactly what to expect.

Now that the analysis is complete what does it reveal? Total volumes dropped by -0.3%. However, there was one additional work day in 2007 vs. 2006 which accounted for a volume increase of 0.4%. As a result, pure volume decrease by -0.7%. Finally, there was a favorable shift in payer mix. There were 1,045 units of volume that moved from the lower reimbursing Medicare group to the higher reimbursing Nominal group.

I try to spend a few minutes each day in the Microsoft Excel Charting forum and it seems like each day someone has a question about pie charts. Today a question was brought up about overlapping data labels in a pie chart with 13 data points. Who cares about the labels . . . what can you see in a pie chart with 13 points? Why do people use pie charts?

This quote is brought to us courtesy of Juice Analytics. Coda Hale writes:

Piecharts are for middle management. Piecharts are the information visualization equivalent of a roofing hammer to the frontal lobe. They have no place in the world of grownups, and occupy the same semiotic space as short pants, a runny nose, and chocolate smeared on one’s face. They are as professional as a pair of assless chaps. Anyone who suggests their use should be instinctively slapped.

For the record, I avoid pie charts like the plague. I've never seen a case where a pie chart performed better than a bar chart or a simple data table.

Below are links to sources that explain how pie charts should and shouldn't be used. They also describe the data visualization problems associated with pie charts. Additional links would be appreciated.

• Making Charts in Microsoft Excel
• The Problem with Pie Charts
• The Chart Doctor from Process Trends