Using Pivot Tables to Summarize Data.

Pivot tables are interactive tables to explore relationships among two or more variables. They are very dynamics and help you and me to see data summarized in many different ways. They include several built-in functions allowing you to: (a) Display counts, average, sums, etc. within categories, (b) Quickly filter data, (c) Create attractive formatted tables, and (d) Easily update tables when underlying data are refreshed.

We will create a PivotTable that include the number of procedures performed by each facility, as well as the average cost, average charge, and average percent markup by facility. As always, there is also available a Video version of these tutorial.

1. Open the file that we have been using for the previous tutorials and create new worksheet, “PivotTableData.” Copy and paste the following columns to your new worksheet
   1. Facility Name
   2. Total Charges
   3. Total Costs
2. Create a new “Markup Rate” variable in column D by calculating (Total Charges – Total Costs) / Total Costs and change the format to “%” with 0 decimal places
   
3. Go to “PivotTableData” worksheet. Highlight the 4 columns of data, Insert —> Place PivotTable report in “New Worksheet.” Relabel new worksheet as “PivotTable”
   
4. The new Worksheet will look like this
   
5. Drag “Facility Name” field to Rows, and drag “Facility Name,” “Total Charges,” “Total Costs,” and “Markup Rate” fields to Values
   
   
6. Edit Value settings using PivotTable menu or by right-clicking on column names in PivotTable field
   1. Facility Name —> Summarize value field as “Count”
   2. Total Charges —> Summarize value field as “Average,” change number format to currency and no decimal places
   3. Total Costs —> Summarize value field as “Average,” change number format to currency and no decimal places
   4. Markup Rate —> Summarize value field as “Average,” change format to % and 0 decimal places         
7. Type more descriptive column names into top row of PivotTable. Your PivotTable should look like this… Look at these results. These are conditional counts (annual procedures) and averages (charge, cost, markup).

1. Click on down arrow in the cell “Facility Name” —> and choose “More Sort Options” to sort by the different fields (annual procedures, average charge, average cost, average markup). Identify the 5 facilities with the highest and lowest average charge. Compare your findings to the Crains’ Report
   
   

1. Copy and paste “APR Severity of Illness” column to “PivotTableData” worksheet (place in column E). Go back to our “PivotTable” worksheet. Choose PivotTable Options —> and Change Source Data. Change table range from PivotTableData!$A:$D to PivotTableData!$A:$E    
2. Add “APR Severity of Illness Description” as a new Report Filter. Filter data by “Moderate” severity only. Sort by average charge.
   

1. By adding the Report Filter (disease severity), we were able to look at summary statistics (counts, averages) for two conditions: (1) disease severity, (2) hospital. Should we get the same results if we used severity of illness as the Row Label, and facility name as our Report Filter? Try it as well as other configurations in your Pivot Table
2. Make your layout similar to this… Try Pivot Charts…

Back to Array Formulas to Calculate the Median and reproduce the Crain’s Report

Our PivotTable gave us a nice initial summary but let’s tackle some other things… Let’s Generate median cost, charge, and markup among patients with moderate disease severity. We will use these functions to rank hospitals, and then evaluate whether the Crains’ Report calculation for % Markup is mathematically correct (you can also follow the video tutorial).

1. Now we want to calculate the median charge, cost, and % markup, by facility, among moderately severe patients. We have two conditions: facility and severity, which requires two IF statements. We will produce a table analogous to our PivotTable, when we added disease severity as a Report Filter. Switch your Pivot Table to the more simple format and sort by Facility Name, and copy and paste Facility Name into column H of the “PivotTableData” worksheet. Add columns for Median Charge, Median Cost, and Median Markup.
2. Enter the Formula for median charge among moderately severe patients at Adirondack Medical Center, using a double IF statement=MEDIAN(IF($A:$A=$H2,IF($E:$E=”Moderate”,B:B)))[1] (Be careful if you copy and paste this formula because the quotation marks can change to “smart” quotation marks giving an error as a result)
   Control + Shift + Enter for the array

1. Drag formulas to the right and format cells. Drag formulas down to populate the rest of the table. Be patient with Excel. This may take some time (In my laptop, it took a couple of minutes). Format the numbers as currency and percentage. Your table should look like this:

1. Highlight cells corresponding to those facilities and delete
   1. Highlight —> Delete —> Shift Cells Up
   2. SAVE your work… Excel can freeze with so many calculations on a large dataset
2. Reorder the Table and find the most expensive facilities or the ones that have the highest markup.

1. Add two new columns:
   1. “Crains Markup”
      = (Median Charge-Median Cost)/Median Cost
      =(I2-J2)/J2 —> Format as a %
   2. “Delta”
      =Median Markup – Crains Markup
      =K2-O2

1. Create your own story with the SPARCS hip data. Develop ONE chart that best illustrates variation in hospital charges. What are the important factors? What additional summary measures should you create? What is the best way to present the data (e.g. pie chart, bar chart, …)?