A recent post to the Microsoft Excel Charting Newsgroup asked "how can I return a non-integer value from a scrollbar? A VBA approach would be to divide the scrollbar output by 10, 100, 1000, etc. Assuming the scrollbar is named "SB1", the example below returns values between 0.1 and -0.1:

Private Sub SB1_Change()
  Range("A1").Value = SB1.Value / 1000
  ScrollBar1.Max = 100
  ScrollBar1.Min = -100
End Sub

Private Sub SB1_Scroll()
  Range("A1").Value = SB1.Value / 1000
  ScrollBar1.Max = 100
  ScrollBar1.Min = -100
End Sub

The post titled Components of Healthcare Revenue Variances identifies a series of high-level gross and net revenue components of change. Of the five components listed (fees, workdays, volume, payer mix, and acuity), three of the five components can be calculated using "pure volume".

The term "pure volume" is a result of how data flows across most revenue systems. Most revenue systems have the ability to report only three of the five components above - volume, acuity, and revenue. For example, if a patient is seen for a normal consultation in a clinic (prefessional) setting, the physician will bill a volume of one (for one consultation performed), an RVU (acuity factor) of 2.33 (a productivity factor relating to the time and complexity of the consultation performed), and revenue of $150 (the amount billed for the consultation performed). The work day and payer mix components are actually a subgroup of volume and must be calculated.

Below is a simple model that illustrates how those variance components might be broken out:

The yellow shaded cells are inputs. The five variance components are listed in column B. The actual components that you can get from a system are listed across in row two.

Column D shows that the volume component extracted from the revenue system can actually be broken down into three subcomponents - pure volume, work days, and payer mix.

For the purposes of this post, "In-Cell" charts can be defined as very small charts, or chart pictures, that cover the height and width of one worksheet cell. Because In-Cell charts are so small, they can be very useful for dashboard reporting.

I've found that it's extremely difficult if not impossible to scale-down and manage one or more Excel charts to the size of a worksheet cell. However, it's not that difficult if the chart is converted to a picture, scaled down, and then positioned into the cell.

The simple report below provides an example of a series of charts scaled down to the size of worksheet cells. The objective of this report is to provide the current month's statistic and a high-level graphic showing the six month trend.

To create the report, I set up the data to be included in each In-Cell chart on the right side of the report outside of the page view. I then calculated a minimum and maximum for each row of data. The data set looks like this:

A single chart is created that uses one row of data as the six-month trend source. I named the chart "Cht1". A macro is used to loop through each row of data, update the chart, copy the chart as a picture, scale the picture down to the size of one worksheet cell, and then place the picture into the appropriate cell. The macro is below:

Sub BuildMicroCharts()

    Application.ScreenUpdating = False

    Dim Rng As Range
    Dim ChtRng As Range
    Dim ChtMax As Range
    Dim ChtMin As Range
    Dim Cht As ChartObject

    Set ChtRng = ActiveSheet.Range("I4:N4")
    Set ChtMax = Range("O4")
    Set ChtMin = Range("P4")
    Set Cht = ActiveSheet.ChartObjects("Cht1")

    For Each Rng In Range("F4:F11")

        ActiveSheet.ChartObjects("Cht1").Activate
        ActiveChart.SetSourceData Source:=ChtRng, PlotBy:=xlRows
        ActiveChart.Axes(xlValue).Select
        With ActiveChart.Axes(xlValue)
            .MaximumScale = ChtMax
            .MinimumScale = ChtMin
            .MajorUnit = (.MaximumScale - .MinimumScale) / 6
            .MinorUnit = (.MaximumScale - .MinimumScale) / 12
        End With

        Cht.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
        Rng.Select
        ActiveSheet.Paste
        
        Selection.ShapeRange.LockAspectRatio = msoFalse
        Selection.ShapeRange.Height = 15
        Selection.ShapeRange.Width = 48

        Set Rng = Rng.Offset(1, 0)
        Set ChtRng = ChtRng.Offset(1, 0)
        Set ChtMax = ChtMax.Offset(1, 0)
        Set ChtMin = ChtMin.Offset(1, 0)

    Next Rng

    Range("A1").Select

    Application.ScreenUpdating = True

End Sub

To delete the series of In-Cell charts, I refered to Ron de Bruin's site which contains many examples of code showing how to delete shapes from a worksheet. The following code deletes each In-Cell chart from the worksheet (the shape type for each In-Cell chart is 13) while preserving the actual chart that serves as the picture source (the shape type for the picture source chart is 3).

Sub DeleteShapes()
    Dim Shp As Shape
    For Each Shp In ActiveSheet.Shapes
        If Shp.Type = 13 Then Shp.Delete
    Next Shp
End Sub

Finally, Ron also provides code that shows the Type numbers of all controls on your worksheet. That code allows you to differentiate between the Type numbers for chart pictures that should be deleted and the actual chart source which you don't want to delete. That code is below:

Sub ListAllObjectsActiveSheet()
    Dim NewSheet As Worksheet
    Dim MySheet As Worksheet
    Dim myshape As Shape
    Dim I As Long

    Set MySheet = ActiveSheet
    Set NewSheet = Worksheets.Add

    With NewSheet
        .Range("A1").Value = "Name"
        .Range("B1").Value = "Visible(-1) or Not Visible(0)"
        .Range("C1").Value = "Shape type"
        I = 2

        For Each myshape In MySheet.Shapes
            .Cells(I, 1).Value = myshape.Name
            .Cells(I, 2).Value = myshape.Visible
            .Cells(I, 3).Value = myshape.Type
            I = I + 1
        Next myshape

        .Range("A1:C1").Font.Bold = True
        .Columns.AutoFit
        .Range("A1:C" & Rows.Count).Sort Key1:=Range("C1"), _
                        Order1:=xlAscending, Header:=xlYes
    End With

End Sub

Using the VBA techniques above, you can quickly scale and position many In-Cell charts in a very short period of time.

In Healthcare financial analysis, it is very important to know who your new patients are vs. your existing patients. New patients are usually defined as a patient that has been seen in the current year but has not been seen for some time period before. For example, a new patient might be a patient that was seen this year (2007) but has not been seen for three years prior (2004, 2005, and 2006). Even if the patient was seen in December 2003, he would still be considered new if seen again in 2007 as per the definition.

Given this definition, the challenge becomes "given X amount of records, how do I write a formula or create a process to count new vs. established patients?" Fortunately Excel makes this easy via the use of pivot tables. For very large amounts of records, Microsoft Access can perform the same function via crosstab queries.

For example, you're boss asks you to quicky identify and count the new vs. established patients for 2007. His definition is that a new patient is one that has been seen at least once in 2007 but not seen for at least two years prior. In other words, a new patient would be one seen in 2007 but not in 2005 or 2006. You are provided a list of medical record numbers (patient numbers), the year in which they were seen, and the charge incurred. Below is that list:

Rather than trying to build formulas to break down the new vs. established patients, first create a pivot table on the entire data set. In practice, the data would probably include millions of records so Microsoft Access and a crosstab query would be the tool of choice. Keep the medical record numbers in the first column while pivoting the years across the columns. The Excel pivot table, or Access crosstab query, would look like this:

At this point it becomes easy to identify the new and established patients. The new patients are highlighted in yellow while the established patient are highlighted in green. As per the definition, the new patients incurred charges in 2007 but not for 2005 or 2006. Conversely, the established patients incurred charges in 2007 but also incurred charges in either 2005 or 2006.

A recent post to the Excel Charting Newsgroup asks "I would like the color of my data points to vary based not on their x or y value, but rather on a third column running alongside them. How can I do it?" A worksheet solution would be to create an XY series for each option that appears in the third column. For example, the screenshot below shows a worksheet with the original XY values in columns A and B. The colors of each point depends on the name entered into column C. Columns E through J contain formulas that determine the series that the data point falls into. If the value in column C is "Jim", the data corresponding to Jim appears in columns E and F. If the value in column C is "Frank", the data corresponding to Frank appears in columns G and H. Finally, if the value in column C is "Kim", the data corresponding to Kim appears in columns I and J.

The formulas are as follows:

The formula in cell E3 is:

=IF($C3="Jim",$A3,NA())

The formula in cell F3 is:

=IF($C3="Jim",$B3,NA())

The formula in cell G3 is:

=IF($C3="Frank",$A3,NA())

The formula in cell H3 is:

=IF($C3="Frank",$B3,NA())

The formula in cell I3 is:

=IF($C3="Kim",$A3,NA())

Finally, the formula in cell J3 is:

=IF($C3="Kim",$B3,NA())

Each of these formulas is copied through their respective ranges. For example, the formula in cell E3 is copied through the range E3 to E11.

Conditional formatting of the range E3:J11 is used to mask the errors. To do so, go to Format -> Conditional Formatting. Enter the formula in the Conditional Formatting dialog box as shown below:

The formatted color is set to white to mask the error messages. Copy the format through the range E3:J11.

The three series that act as the source for the XY Scatter Chart are entered into the Chart Source Data dialog box. An example showing the first series "Jim" is shown below:

The example showing the second series "Frank" is below:

Finally, the last example showing the third series "Kim" is below:

A second option would be to use VBA to color each point. An example of a VBA routine to do so is below:

Sub ChangeXYColors()
    Application.ScreenUpdating = False
    Dim Rng As Range
    Cnt = 1
    For Each Rng In Range("C3:C11")
    ActiveSheet.ChartObjects("Chart1").Activate
    ActiveChart.SeriesCollection(1).Points(Cnt).Select
        If Rng.Value = "Jim" Then
            With Selection
                .MarkerBackgroundColorIndex = 5
                .MarkerForegroundColorIndex = 5
            End With
        ElseIf Rng.Value = "Frank" Then
            With Selection
                .MarkerBackgroundColorIndex = 3
                .MarkerForegroundColorIndex = 3
            End With
        ElseIf Rng.Value = "Kim" Then
            With Selection
                .MarkerBackgroundColorIndex = 10
                .MarkerForegroundColorIndex = 10
            End With
        End If
    Cnt = Cnt + 1
    Next Rng
ActiveChart.Deselect
End Sub

The worksheet option and the VBA option both return a chart that looks like this:

David A. Heiser writes a very comprehensive essay titled Errors, Faults, and Fixes for Statistical Functions and Routines in Excel.

Concerning charts in Excel 2007, David writes "Building charts has completely changed, and their appearances also completely different. There is an increased ability to put in chartjunk’, lighting effects, shading, 3D renderings, flashy, distracting figures, silly variations, insertion of icons, visual distractions, etc. This is what the business world wants, the ability to insert effects to obscure, bias or just to add variety to frequent presentations."

Interesting. Something that I deal with all the time at work is the need to use 3D renderings and other worthless visual effects for presentations. When I object most people look at me like I've got two heads. Am I just wrong? Thoughts?