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>
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Watch MrExcel Video

Forum statistics

Threads
1,100,206
Messages
5,473,157
Members
406,849
Latest member
AndreasMs

This Week's Hot Topics

Top