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?

The following chart-related add-ins were recently updated:

• Chart Patterns Fill Add-In - AJP Excel Information
• Change Series Formula Add-In - Peltier Technical Services
• Box and Whisker Plots Add-In - Peltier Technical Services
• Waterfall Charts Add-In - Peltier Technical Services

Each of these add-ins is available as a free download via the links above.

Updates to selected sites with RSS feeds are available via the RSS Updates page of this site.

Excel's "Add Trendline" option automatically provides an equation for the Linear Regression option. The model below shows how you can manually create that equation if needed. Also included is a calculation for correlation.

The formula for the slope (cell F18) is:

=((D13*G13)-(E13*F13))/(D13*H13-E16)

The formula for the Y intercept (cell F19) is:

=(F13-F18*E13)/D13

Although not a part of the linear regression calculations, you might find it useful to know how to manually calculate the correlation of the X and Y data. The formula to calculation correlation (cell F20) is:

=(((D13*G13)-(E13*F13))/SQRT((D13*H13-E16)*(D13*I13-F16)))^2

A chart showing the original points and trendline is below:

The Excel-generated trendline formula appears at the top right side of the chart. The manually calculated trendline in the example starts in cell F26 and is copied through the range F26:F33. That formula is below:

=$F$18*E26+$F$19

At a very high-level, the components of healthcare gross and net revenue can be broken down into these areas:

• Fees - the amount charged for a service.
• Volume - the units of service charged.
• Work Days - the amount of days worked in a particular period.
• Payer Mix - the type of insurance reimbursement that a patient may present.
• Acuity - the level of complexity of a service performed.

Complicating the matter, the total change in actual Volume is made up of Pure Volume, Work Days, and Payer Mix.

Payer Mix can be broken down into five different areas:

• Commercial Payers - the amount reimbursed for a service by Commercial insurance payers.
• Contract Payers - the amount reimbursed for a service by payers who have contracted with the healthcare provider.
• Employees - the reimbursement received for employee provided healthcare services.
• Medicare - the reimbursement received for Medicare provided healthcare services.
• Other Government - the reimbursement received for Government provided healthcare services exclusive of Medicare.

Given the components above, there can be many variations of the types of budget and variance analysis that can be performed. In future posts, I'll show some of the Excel modeling and charting that I've used to analyze and explain these components.

Today I've added an additional category to this weblog. The category, titled "Healthcare Analysis", will contain entries showing how to utilize Excel to perform financial analysis and budgeting in a Healthcare environment. All of these entries are based on real on-the-job requirements and requests.

To frame the discussion, I've worked for a worldwide Healthcare provider in the Phoenix / Scottsdale area for about ten years. It is a combined Clinic and Hospital system with approximately 4,000 employees in Arizona and 42,000 worldwide. As a result many of the examples will be very high-level.