Moving Average in Pivot Table Calculating off Percentages

stefaniem

New Member
Joined
Nov 7, 2008
Messages
1
Moving Average in Pivot Table Calculating off Percentages- <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Need it to Calculate off Actuals (Need to Use a Range)<o:p></o:p>
<o:p> </o:p>
I have a pivot table that filters by three data fields: Card, Merchant, Exit Page.<o:p></o:p>
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:p> </o:p>
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:p></o:p>
<o:p> </o:p>
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:p> </o:p>
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:p></o:p>
<o:p> </o:p>
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:p></o:p>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Forum statistics

Threads
1,136,768
Messages
5,677,624
Members
419,707
Latest member
Anna vib

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top