There are times when you need to plot data on both a primary axis and a secondary axis. Creating charts for correlation analysis is one example. Although Excel allows you to easily create these types of charts, unfortunately it does not offer an option to create proportionate axes. To do so you must perform the calculations yourself and then enter the scale values individually.

For selected primary and secondary axis presentations, it is important to make sure that each axis is proportionate to the other. If the axes are not proportionate, you run the risk of misrepresenting the data. The proportionate axes insures that the data is presented in the same plane.

Most of the time the calculations to create proportionate axes are fairly easy to do. This is especially true when the minimum and maximum values for both the primary and secondary axis are positive. However, the calculations get complex if negative values fall into the mix. Fortunately, I've seemed to have found a simple calculation that seems to work for all values positive and negative.

As an example and given the data set below, the task is to create a chart with a primary and secondary axis. Both axes must be proportional to each other and must have tick marks that line up with each other.

Before showing how to create the chart, below is the end result:

How do you know that the axes are proportional to each other? To test the results, divide the absolute value of the maximum axis value by the absolute value of the minimum axis value. Using the example above, the primary axis ratio is calculated as:

abs(-122.99268) / abs(59.47431) = 2.067997

The secondary axis ratio is calculated as:

abs(489,195) / abs(-236,555) = 2.067997

Since both ratios equal each other, the axes are proportionate.

Calculating the Proportionate Axes Values

To calculate the values for the proportional axes, three sets calculations are performed. The first two calculations provide data for the final calculation. The final calculation is then used as the source for the axes values. Based on the data above, the worksheet with all three calculations looks like this:

Axes Calculation 1

For Axes Calculation 1, one possible option for the primary axes is the maximum of the range C3:C12. Another possible option for the primary axes the minimum of the range C3:C12. The midpoint is the middle value between the maximum and the minimum as calculated with this formula:

=MAX($C$3:$C$12)-((MAX($C$3:$C$12)-MIN($C$3:$C$12))/2)

A possible option the secondary axes (cell D17) is calculated with this formula:

=C16/C17*D17

Another possible option for the secondary axes (cell D19) is calculated with this formula:

=C18/C17*D17

The midpoint for the secondary axes is calculated the same as shown above.

Axes Calculation 2

For Axes Calculation 2, a possible option for the secondary axes is the maximum of the range D3:D12. Another possible option for the secondary axes the minimum of the range D3:D12. The midpoint is the middle value between the maximum and the minimum as calculated with this formula:

=MAX($D$3:$D$12)-((MAX($D$3:$D$12)-MIN($D$3:$D$12))/2)

A possible option for the primary axes (cell E17) is calculated with this formula:

=F16/F17*E17

Another possible option for the primary axes (cell E19) is calculated with this formula:

=F18/F17*E17

The midpoint for the primary axes is calculated the same as shown above.

Final Axes Calculation

Once the first two calculations are complete, the maximum and minimum values for the proportional axes can be obtained. To get the maximum value for the primary axis, take the maximum of calculations 1 and 2. The formula in cell D24 is:

=MAX(C17,E17,C19,E19)

To get the minimum value for the primary axis, take the minimum of calculations 1 and 2. The formula in cell D25 is:

=MIN(C17,E17,C19,E19)

To get the maximum value for the secondary axis, take the maximum of calculations 1 and 2. The formula in cell E24 is:

=MAX(D17,F17,D19,F19)

To get the minimum value for the secondary axis, take the minimum of calculations 1 and 2. The formula in cell E25 is:

=MIN(D17,F17,D19,F19)

Enter the values obtained above as the maximum and minimum primary and secondary axis values. To align the tick marks, divide the difference between the maximum and minimum values for each axis by the same number. This example divides the differences by six to get the major units. For example, to get the primary major unit values, subtract the minimum value (-122.99268) from the maximum value (59.47431). The difference is 182.46699. Divide that difference by six and enter the result as the major unit. Do the same for the secondary axis and your axes are complete.

Please note that these calculations do not take into account the possibility that the midpoint of one, the other, or both axes falls at zero. This issue will be discussed at a later date.

Recently a project at work required that I do the following:

• Create two charts - one showing the top "X" percent of total revenue and the other showing the remaining "Y" percent of revenue. "X" and "Y" needed to be variable depending on the variability of the source data as well as how the user decided to present the data.
• In addition to the first chart showing the top "X" percent of revenue, it had to contain a bar at the end that showed the total "Y" percent of revenue that was to be detailed on the second chart.
• The data in both charts had to be sorted high-to-low with the exception "Y" bar of the first chart that needed to appear at the end.
• The data had to be set up in an static input format so that someone with little knowledge of Excel could update the charts.

Before explaining how to build, the final charts looked similar to these:

Chart 1

Chart 2

The first step in building the worksheet was to create a static range for data input. This range is reflected in cells C6:C25. This static data input range allows someone with very little spreadsheet knowledge could walk down each of the departments, which are in alphabetical order, and enter the data for that department.

The second step in building the worksheet was to create three helper columns. The first two helper columns covering the range D6:E25 contained formulas that sorted the data in the input range C6:C25. The screenshot below shows the worksheet.

The formula in cell E6 is below. This formula sorts the data in the input range C6:C25 from high-to-low. It is copied through the range E6:E25.

=LARGE($C$6:$C$25,ROWS($C$6:C6))

The formula in cell D6 is below. This formula looks up the department values based on the sorted data in the range E6:E25. It is copied through the range D6:D25.

=INDEX($B$6:$B$25,MATCH($E6,$C$6:$C$25,0))

The reminder of the revenue that makes up the last bar in the first chart is reflected in cell E27. The formula in that cell is:

=SUMPRODUCT(($F$6:$F$25=2)*($E$6:$E$25))

The third helper column covers the range F6:F25. The purpose of this range is to assign the data to charts one or two depending on where each department falls as a percentage of total revenue. The formula in cell F6 is below. It is copied through the range F6:F25. It works by building a cummulative sum of the data and comparing that sum to the total sum of the data. If the percent of revenue is less than the value in cell D2, the revenue is assigned to chart one. If it is more than the value in cell D2, it is assigned to the second chart.

=IF(SUM($E$6:E6)/SUM($E$6:$E$25)<$D$2,1,2)

Once the worksheet was built, the key to building the charts is utilizing a series of dynamic range names as the chart source. Because the first chart had to reflect the top "X" percent of revenue and a summary bar showing the remaining revenue, the chart had to be based on two non-contiguous data sources.

To create the first chart four named ranges needed to be created. The first named range, called "Cht1Data", makes up the first non-contiguous range. It is a dynamic formula (the range changes depending on what is entered into cell D2, and consists of the formula:

=OFFSET(Sheet1!$E$6,0,0,COUNTIF(Sheet1!$F$6:$F$25,1),1)

The formula appears in the named range dialog box as:

The second named range, called "Cht1Remainder", makes up the second non-contiguous range. It picks up the remainder of the revenue shown in cell E27. It consists of the formula:

=Sheet1!$E$27

The formula appears in the named range dialog box as:

Both of these named ranges will need to have corresponding X-Axis values assigned. To do so, two more named ranges are created. The first named range assigns the X-Axis values to the "Cht1Data" range. It is called "Cht1XAxis" and consists of the formula:

=OFFSET(Sheet1!$E$6,0,-1,COUNTIF(Sheet1!$F$6:$F$25,1),1)

The formula appears in the named range dialog box as:

The second named range assigns the X-Axis values to the "Cht1Remainder" range. It is called "Cht1XRemainder" and consists of the formula:

=Sheet1!$B$27

The formula appears in the named range dialog box as:

Now that the range names are created for the first chart, it's time to create the chart. To do so, the range names need to be added to the series and X-Axis in a non-contiguous format. Below are the source values for the first chart.

Assuming the Excel file is called "NCR.xls", the source for the first series is:

=(NCR.xls!Cht1Data,NCR.xls!Cht1Remainder)

Note that a comma separates the two range names and that the source is surrounded by parenthesis.

The source for the X-Axis is:

=(NCR.xls!Cht1XAxis,NCR.xls!Cht1XRemainder)

The process of building the second chart is exactly the same as the first chart - just a little easier. This chart only requires two named ranges. The first named range, called "Cht2Data", consists of the formula (enter the formula into the named range dialog box as shown in the first examples above):

=OFFSET(Sheet1!$E$6,COUNTIF(Sheet1!$F$6:$F$25,1),0,COUNTIF(Sheet1!$F$6:$F$25,2),1)

The second named range, called "Cht2XAxis", consists of the formula (enter the formula into the named range dialog box as shown in the first examples above):

=OFFSET(Sheet1!$E$6,COUNTIF(Sheet1!$F$6:$F$25,1),-1,COUNTIF(Sheet1!$F$6:$F$25,2),1)

Below are the source values for the second chart.

A recent post to the Microsoft Excel Charting Discussion Group asks "how can I create a pie chart that references a fixed data source and excludes all zero values?" Below is an example of how to do so . . .

Step 1 - Creating the Worksheet

First, this example assumes that the workbook is called "PNZ.xls". Assume your original data set is in the range B4:C13.

The purpose of the formulas in column D is to slightly change the numbers in column C to allow them to continue to sort high-to-low if there are duplicates. The formula below is in cell C4. It is copied down the column to cover the range D4:D13.

=IF(C4=0,0,C4-ROW()/10^10)

A new range containing formulas that exclude zero values is created in cells E4:F13.

The formula in cell F4 is below. It is copied down the range F4:F13.

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

The formula in cell E4 utilizes the Index and Match functions to perform a left-sided lookup on the data values returned in column F. It is copied down the range E4:E13.

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

Step 2 - Building the Chart

The chart uses defined names based on the data and data label ranges to automatically update. Two range names need to be created. In this example the first named range is called "Data". The formula that makes up the "Data" range appears in the screenshot below:

The second named range is called "DataLabels". The formula that makes up the "DataLabels" range appears in the screenshot below:

The source of the pie chart is based on the Data and DataLabels defined names. The chart source data dialog box containing series references to the two defined names (Data and DataLabels) is shown below.

Finally, the completed pie chart appears below.

In the screenshot below, the value 425 has been added to cell C6. Note how the chart source (columns E and F) has automatically updated to include the new entry in row 7:

The pie chart has automatically updated to include the new value:

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.