title: How to get an average for each hours for the given time period using Pivot Table?
body: <p>Something I'm trying on Pivot Table but haven't been able to. Not sure if it can be done or not, so thought I would ask the experts. </p>
  
  <p>I have relatively small dataset in excel for Oct, Nov, and Dec with patient and the procedure canceled. I would like to know what is the average number of cancellation by hour for these 3 months. Here's the sample of the data: </p>
  
  <pre><code>Date        Appt Time   Rounded Time        Canc Reason Canc. Comments.                                                         Department      Procedure
  10/25/2017   4:30 AM    10/25/17 4:00 AM    Other                                                                               NONINVASIVE     ECG 12-LEAD (CHEAT LAKE CLINIC ONLY) [ECG3047]
  11/2/2017    6:25 AM    11/2/17 6:00 AM     Error                                                                               NONINVASIVE     ECG
  11/21/2017   7:00 AM    11/21/17 6:00 AM    Other       per pt home health nurse--did not know about appt time                  NONINVASIVE     TTE
  10/16/2017   7:15 AM    10/16/17 6:00 AM    Error       PVR just completed not needed Holiday RN                                NONINVASIVE     TEE
  10/9/2017    7:15 AM    10/9/17 6:00 AM     Error       error in swcheduling - not needed per ordering                          NONINVASIVE     TTE
  11/16/2017   7:30 AM    11/16/17 6:00 AM    Other       pt not coming                                                           NONINVASIVE     TEE
  11/15/2017   7:30 AM    11/15/17 6:00 AM    Other                                                                               NONINVASIVE     HOLTER MONITOR 
  </code></pre>
  
  <p>I want to create a Pivot Table based on this data. I want to know how many cancellation were done every couple of hours (based on <code>Rounded time</code>) for each procedure type. I can easily do the count on this, but that wouldn't give me an idea of how many <code>on average</code> we're getting cancellation on those hours. This would help us manage more patients if there are cancellations. </p>
  
  <p>In this method, I had hours in columns and <code>procedure</code> and <code>cancellation reason</code> in rows; <code>cancellation reason</code> being under each <code>procedure</code>.</p>
  
  <p>One method I applied is by adding a column called <code>count</code> with value <code>1</code> for all rows. Then I used that in Pivot table to get the <code>sum</code> of the <code>count</code>. This gave me the same result as above. When I did <code>Average</code> on this column, all I get is value <code>1</code>, and it makes sense why it's doing that. </p>
  
  <p>So, the question is, how do I get an average of cancellation for these hours for the given time period? If it can't be done using Pivot Table, what's the other option I have that I can use to get the result I'm looking for? </p>