Average Function & PivotTable Question

[vachb]

Hello:
First time Post Here so bear with me:

I have a data set with numbers and some of the numbers are "0".

Appointments Made Appointments Cancelled Ave Cancel Rate %
11 4 36%
5 0 0%
6 2 33%

I am utilizing the PivotTable Function. I am calculating an average Cancel Rate. The PivotTable is not including the 0% when calculating the over all average. I get a return of 35% when it should be 23%. Is there a way around this within the PivotTable? In my main data set I am calculating the average Cancel rate each day and I am trying to come up with a monthly average. That is my main goal. Any suggestions are greatly appreciated!!
Thanks.
Bill

 

[RoryA]

Are you calculating the rate in the pivot, or in the underlying data? If you use a calculated field in the pivot, you should get the answer you want.

 

[vachb]

If I am understanding the question correctly:

In the Pivot Table, the "Cancel Rate" is in the value field and In the Value Field Settings, I summarize the value field by choosing "Average" and use "percentage" for the number format.

I calculate the cancel rate percentage in the underlying Data and I think this is where my mistake is but I'm not sure??

 

[RoryA]

Can you clarify what you are expecting as the answer? Do you want a straight average of 36,33 and 0 (23%), or the weighted average of the three - i.e. (4+0+2)/(11+5+6)= 27.3%

 

[vachb]

After reading your post, I guess I am after the weighted average. So this lead to the question, can I get this "answer" using the pivot table with the current underlying data? Or Do I need to add a column the data set with the SUM formula?

 

[Jared_8878]

put a calculated field in your pivot table

it should look like this:

=SUM('App''s canceled' )/SUM('App''s made' )

 

[RoryA]

No need for the Sum in there. Just set the field to Sum in the table.

 

[Jared_8878]

No need for the Sum in there. Just set the field to Sum in the table.

thanks.


='App''s canceled'/'App''s made'

 

[vachb]

Thanks for the assistance!