Excel 2007 Pivot Table - Subtotal, Avg, Std Dev 1

[BxWill]

Have two pivot tables that I have to modify rather quickly during a Trend review and not able to perform.

Pivot 1

Column Labels
Level (1 through 4)
Period_Review ("Prior" or "current")

Values
Visits

Row Labels
Customer Name


questions

1. How do I add subtotals for the number of visits in the rows for each provider, by the Period Review Field (Prior or current)?

2. How do I display the difference between the values in the Period_Review field (i.e. Current - Prior) for each level?

3. How do I display the percentage change (i.e. from Prior to Current) for each level?






Pivot 2

Column Labels
Level (1 through 4)
Period_Month_Nm (Jan2007,Feb2007,Mar2007,Jan2008,Feb2008,Mar2008)

Values
Visits

Row Labels
Customer Name


questions

1. How do I display the total and average number of visits for the Prior period (Jan 2007, Feb2007,Mar2007) and the current period (Jan2008, Feb2008, Mar2008).

2. Then, calculate the standard deviation for each month during the prior periods and for each month during the current period.

3. Would like to automate this process as well due to the need to perform every month.

Currently reviewing the Help section but need some quick insight as to if all of this is possible.

Would also appreciate if anyone know of any additional resources or downloads that I can reference.


Thanks in advance.

 

[SkipVought]

Hi,

Your questions are complex and the cases, from my perspective, are very vague.

PTs are a powerful summarization tool.

If you have REAL DATES IN EVERY ROW (NO BLANKS or ILLEGAL DATES), you can GROUP by Month/Year, Quarter/Year, Year, Week, Fortnight ... lots of grouping possibilities there.

If you have some special cases you need quick answers, make a separate PT with the separate criteria fields in the UPPER L-H area and the aggregation(s) you need.

Alternatively, using your source data, write some COUNTIFS, SUMIFS, AVERAGEIFS... formulas with the criteria values referenced to cells where you can enter specivic valued or have a Data > Validation > List for a dropdown of legal values, OR use Database functions to perform the same in a similar fashion, where you can use more complex criteria.

Short of seeing a specific example, and what you need to happen, I have no speicifc suggestions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}