A recent post to the Microsoft Excel Charting Discussion Group asks "how can I sort charts on a worksheet by name?" The code below loops through all of the charts in the active sheet and loads their names into an array called "arrChartNames". The array is then sorted via the Array_Sort function. The sorted results are loaded into the Buffer variable and the charts are finally placed in alphabetical order via the For-Next loop.

Sub SortChartNames()

    Dim arrChartNames()
    Dim Cht As ChartObject
    Dim Buffer As Variant
    Dim Rng As Range

    X = 0

    For Each Cht In ActiveSheet.ChartObjects
        ReDim Preserve arrChartNames(X)
        arrChartNames(X) = Cht.Name
        X = X + 1
    Next Cht

    Buffer = Array_Sort(arrChartNames)

    Z = 2
    
    For Each X In Buffer
        ActiveSheet.Shapes(X).Top = Z
        ActiveSheet.Shapes(X).Left = 10
        Z = Z + 90
    Next X

End Sub

Private Function Array_Sort(ByVal arry As Variant) As Variant

    Dim i As Long
    Dim j As Long
    Dim k As Variant

    For i = LBound(arry) To UBound(arry)
        For j = i + 1 To UBound(arry)
            If arry(i) > arry(j) Then
                k = arry(j)
                arry(j) = arry(i)
                arry(i) = k
            End If
        Next
    Next
    Array_Sort = arry

End Function

In a medical practice, billing codes are typically arranged in a hierarchy. CPT4 codes are at the top tier with procedure codes as a secondary tier. Each procedure code is attached to a fee. Although the procedure codes can differ, generally all fees within the same CPT4 range should be the same.

Most practices have many thousands of procedure codes and fees in their billing systems. Due to the nature of data input, as these database grow the odds of input errors also grow. For that reason it is necessary to periodically audit the data in the system for error.

The screenshot below shows a simple database containing CPT4 codes, procedure codes, and fees. The arrows indicate fees that were entered in error.

Without some automated process, finding these errors can be extremely time consuming and costly. If not found in a timely manner, bills with errors are sent to patients which require adjustments once found.

The macro below helps to automate the process of finding these errors. The macro scans the first column for like CPT4 codes. It then takes the average of all of the fees corresponding to those CPT4 codes. If the average differs from the last fee in the CPT4 range, the corresponding data range is highlighted in yellow signifying a data input error.

Sub FlagErrors()
    Dim Rng As Range
    Dim Cnt As Integer
    Dim Avg As Variant
    Dim Oset As Integer
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    
    '******
    'Inputs
    '******
    '(1) Sets the Search Range at Column A
    Set myRange = ActiveSheet.Range("A2:A" & LR)
    '(2) Sets the Data Range as the Offset to Column A
    Oset = 2
    '******
    
    Cnt = 1
    For Each Rng In myRange
        If Cnt = 1 Then X = Rng.Offset(0, Oset).Address
        If Rng.Value = Rng.Offset(1, 0).Value Then
            Cnt = Cnt + 1
            Rng = Rng.Offset(1, 0)
        ElseIf Rng.Value <> Rng.Offset(1, 0).Value Then
            Y = Rng.Offset(0, Oset).Address
            Cnt = 1
            Set CodeRange = Range(X & ":" & Y)
            Avg = Application.WorksheetFunction.Average(CodeRange)
            If Avg <> Rng.Offset(0, Oset).Value Then
                CodeRange.Interior.ColorIndex = 6
            End If
        End If
    Next Rng
End Sub

The audited spreadsheet looks like the one below. At this point the errors can be quickly corrected.

When forecasting it's often useful to know what the compounded rate of change over a given period of time is for a selected variable. As an example, let's say a pharmacy dispenses 50ml of saline in period 1. The following periods it dispenses 57.17ml, 59.25ml, 64.23ml, 65.18ml, and 71.50ml. The actual rates of change for each period are 14.34%, 18.50%, 28.46%, 30.36%, and 43.00% respectively. What formula can you use to calculate a compounded rate of change which will in turn allow you to build a simple linear forecast of how much saline will be dispensed into the near future?

Simply put, the formula is:

((Last Period Data / First Period Data) ^ (1 / Number of Periods)) - 1 

Given the example above, the formula returns a compounded rate of change equaling 7.42%.

The compounded rate of change is calculated as:

(( 71.50 / 50.00 ) ^ ( 1 / 5)) - 1

The result can be proved out as follows:

Finally, a chart visually shows the actual growth (blue line) vs. the compounded growth (red line):

Given the compounded growth rate of 7.42%, the screenshot below shows how the rate is applied to the historical data to estimate two periods into the future (periods six and seven).

The chart has been modified to include the projection periods six and seven:

Please note that this is a very simplistic example that is useful for statistical forecasting only.

From Microsoft, this Knowledge Base article provides a macro that allows the user to extract data from a chart.

In Microsoft Excel, you can retrieve data from a chart even when the data is in an external worksheet or workbook. This is useful in situations where the chart was created from, or linked to, another file that is unavailable or has been damaged in some way. When the source data to a chart is lost, the data can still be retrieved from the chart itself, by using a Microsoft Visual Basic for Applications macro.

First add a worksheet into your workbook and call it "ChartData". Next, add the following macro to a module within your workbook. Finally, run the macro to extract the chart data.

Sub GetChartValues()
   Dim NumberOfRows As Integer
   Dim X As Object
   Counter = 2

   ' Calculate the number of rows of data.
   NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values)

   Worksheets("ChartData").Cells(1, 1) = "X Values"

   ' Write x-axis values to worksheet.
   With Worksheets("ChartData")
      .Range(.Cells(2, 1), _
      .Cells(NumberOfRows + 1, 1)) = _
      Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
   End With

   ' Loop through all series in the chart and write their values to
   ' the worksheet.
   For Each X In ActiveChart.SeriesCollection
      Worksheets("ChartData").Cells(1, Counter) = X.Name

      With Worksheets("ChartData")
         .Range(.Cells(2, Counter), _
         .Cells(NumberOfRows + 1, Counter)) = _
         Application.Transpose(X.Values)
      End With

      Counter = Counter + 1
   Next

End Sub

This example allows a fixed range for inputs, automatically sorts the results, and charts those results. The input area and chart source look like this:

The formulas in column D calculate a very small number that is subtracted from the original. The purpose is to prevent possible duplication of entries. Below is the formula in cell D4.

=C4-ROW()/10^10

The formulas in column F sort the data in column D from highest to lowest value. Below is the formula in cell F4.

=LARGE($D$4:$D$13,ROWS($B$4:B4))

Finally, the formulas in column E utilize the INDEX and MATCH worksheet functions to perfrom a left-sided look-up on the data in column F. Below is the formula in cell E4.

=INDEX($B$4:$B$13,MATCH($F4,$D$4:$D$13,0))

The chart below provides the end-result.

These entries showed how to perform a variance analysis given two, three, or four components of variance:

• Variance Analysis - Two Components
• Variance Analysis - Three Components
• Variance Analysis - Four Components

The logic illustrated in these posts can be applied to annual and monthly forecasting models. The example below illustrates how one could build a monthly salary forecasting model given a salary, FTE, and paid day history and projection. The example assumes that the salaries and FTEs for the period spanning January 2007 through April 2007 are given. The task is to project salaries on a per-month basis given a 4% salary increase that occurs in October 2007, a 5% salary increase that occurs in August 2008, an increase of 1 paid day between 2007 and 2008, and an increase of 11 FTEs that occur between 2007 and 2008.

The cells shaded yellow provide inputs to the template. Of note is the trend in salary rate which is shown in the chart below. The white bars show the actual rate and the blue bars show the projected rate. The salary rate, given the FTE, paid day, and average salary calculations, proves to be a step-function as it moves from $1.10 to $1.15 to $1.20 based on when the salary increases occur.

In addition to salaries, models that forecast and spread revenue, discounts, benefits, supplies, services, and more can be created based on the logic in this template.