Moving Average in Pivot Table Calculating off Percentages- <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
Need it to Calculate off Actuals (Need to Use a Range)<o></o>
<o> </o>
I have a pivot table that filters by three data fields: Card, Merchant, Exit Page.<o></o>
My row value is the date. My column vlaues are three calculated fields: Application Rate, Approval Rate, and Sales Rate. These are all percentages.
<TABLE style="WIDTH: 626px; BORDER-COLLAPSE: collapse; HEIGHT: 78px" cellSpacing=0 cellPadding=0 width=626 border=0><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 466pt; mso-width-source: userset; mso-width-alt: 22710" width=621><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 50pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=67 height=20>SalesRate</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 466pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=621>=sales_count/click_count</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>AppRate</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">=application_count/click_count</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>AprRate</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">=IF(sales_count>application_count,0,(IF(application_count=0,0,sales_count/application_count)))</TD></TR></TBODY></TABLE>
<o> </o>
My team asked me to give them a 7-Day Moving Rate so I used the built in Moving Average trend line; however, this is calculating using the percentages which is not what my team wants.<o></o>
<o> </o>
I need Excel to calculate the daily rates by dividing using the total (clicks, applications, sales) for seven days, basically a moving weighted average. I can't calculate this in the raw data since it changes depending on the filter criteria (unless someone knows of a way to accomplish this!).
<o> </o>
The only workaround I have found is doing a completely separate Pivot Table that shows the clicks, application and sales totals by day and then using GETPIVOTDATA to do totals off to the side and then chart this data. My team wants this series to be on the original pivot table but I don’t think it is possible to do that.<o></o>
<o> </o>
I hope there is something I am missing that will allow me to do the 7-day moving weighted average rates on my original pivot table.<o></o>
fficeffice" /><o></o>Need it to Calculate off Actuals (Need to Use a Range)<o
></o><o
> </o>I have a pivot table that filters by three data fields: Card, Merchant, Exit Page.<o
></o>My row value is the date. My column vlaues are three calculated fields: Application Rate, Approval Rate, and Sales Rate. These are all percentages.
<TABLE style="WIDTH: 626px; BORDER-COLLAPSE: collapse; HEIGHT: 78px" cellSpacing=0 cellPadding=0 width=626 border=0><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 466pt; mso-width-source: userset; mso-width-alt: 22710" width=621><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 50pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=67 height=20>SalesRate</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 466pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=621>=sales_count/click_count</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>AppRate</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">=application_count/click_count</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>AprRate</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">=IF(sales_count>application_count,0,(IF(application_count=0,0,sales_count/application_count)))</TD></TR></TBODY></TABLE>
<o
> </o>My team asked me to give them a 7-Day Moving Rate so I used the built in Moving Average trend line; however, this is calculating using the percentages which is not what my team wants.<o
></o><o
> </o>I need Excel to calculate the daily rates by dividing using the total (clicks, applications, sales) for seven days, basically a moving weighted average. I can't calculate this in the raw data since it changes depending on the filter criteria (unless someone knows of a way to accomplish this!).
<o
> </o>The only workaround I have found is doing a completely separate Pivot Table that shows the clicks, application and sales totals by day and then using GETPIVOTDATA to do totals off to the side and then chart this data. My team wants this series to be on the original pivot table but I don’t think it is possible to do that.<o
></o><o
> </o>I hope there is something I am missing that will allow me to do the 7-day moving weighted average rates on my original pivot table.<o
></o>
