From Microsoft - Knowledge Base Article 213644:

In Microsoft Excel, you can set the Minimum and Maximum properties of the y-axis scale to any value. The following macro sets the minimum and maximum values for the y-axis scale in a chart to the maximum and minimum values of the data used to create the chart.

Sub SetScaleToMinAndMaxValues()
    Dim ValuesArray(), SeriesValues As Variant
    Dim Ctr As Integer, TotCtr As Integer
    With ActiveChart
        For Each X In .SeriesCollection
            SeriesValues = X.Values
            ReDim Preserve ValuesArray(1 To TotCtr + UBound(SeriesValues))
            For Ctr = 1 To UBound(SeriesValues)
                ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr)
            Next
            TotCtr = TotCtr + UBound(SeriesValues)
        Next
        .Axes(xlValue).MinimumScaleIsAuto = True
        .Axes(xlValue).MaximumScaleIsAuto = True
        .Axes(xlValue).MinimumScale = Application.Min(ValuesArray)
        .Axes(xlValue).MaximumScale = Application.Max(ValuesArray)
    End With
End Sub

It you need to use "=NA()" as a source value, the macro above fails. However, you can modify the macro to allow the array to accept only numeric values. To do so, modify the For-Next procedure as follows:

For Ctr = 1 To UBound(SeriesValues)
    If IsNumeric(SeriesValues(Ctr)) Then
        ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr)
    End If
Next

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.

I've recently updated the Formula Trace Tool utility to include a few new features. Behind the scenes, the formula auditing code has been streamlined to run more efficiently. If interested, please visit the Downloads page for more information.

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:

The need to fill a blank range with zeros often occurs when dealing with data that is downloaded from a mainframe. Recently I've seen several examples that utilize a loop structure to fill a blank range with zeros. Rather than using loops, below is an example that utilizes the SpecialCells method to fill a range.

Sub FillBlanksWithZeros()
    Dim Rng As Range
    On Error GoTo ErrHandler
    Set Rng = Range("A1:B10")
    Rng.SpecialCells(xlCellTypeBlanks) = 0
    Exit Sub

ErrHandler:
    MsgBox "Error # " & Err.Number & " - " & Err.Description
End Sub

The SpecialCells method is much faster than using a loop structure.

Before distributing a report as an Excel file, I always add or make sure of the following (in no particular order):

• A personal preference - use Arial or Verdana font. I've found these two fonts to be the most professional as well as the easiest to read on paper, on screen, or on the web.
• A descriptive title. I usually use three lines - the first is the company name, the second is the title of the report, and the third is the time period that the report covers.
• If the report contains confidential information, add the word "Confidential" to the header.
• If there are more than four or five lines or rows of information, use line numbers or alpha references on the left side of the report to quickly identify those lines or rows.
• If there are more than four or five columns of information, use column numbers or alpha references above the column titles to quickly identify those columns.
• Format dollars as dollars and statistics as numbers. A dollar sign ($) in front of any dollar value always insures that the viewer knows the difference between dollars and numbers.
• A footer that identifies the location of the report on the network as well as other information such as who created the report, the date and time created, page numbers, etc.
• Always remember to set up the print range before distributing the report.
• If the report contains cells with formulas that might are return errors, remove the errors or use conditional formatting to mask the errors before distribution.
• If the Excel file with report contains macros, I usually remove them before distribution so that whoever receives the report does not have to respond to macro prompts when the file is opened.
• Protect the sheets against unwanted changes if needed. I added this idea but rarely do this in practice. If I really want to protect an Excel based report from someone else making changes after distribution, I send it out as an Adobe .pdf file.

Am I missing anything?