Pivot table question

[jesoldano]

Hello--I have a very basic pivot table that counts the number of time
an item is a spreadsheet. I would like to write formulas outside of
the pivot table that calculate the average of all of the items, as
well as the standard deviation. The only problem is that, when I
modify the spreadsheet feeding the pivot table and refresh it, the
ranges in my formulas are no longer correct. Is there a way to make
these ranges update automatically when the pivot is refreshed? Or,
should I just do this manually everyday? Thanks for the help!

 

[Barb Reinhardt]

YOu could create a dynamic range to do it and then calculate

=STDEV(myRange)

Take a look here for instructions on setting up the dynamic range.

http://www.contextures.com/xlNames02.html

The only problem I can foresee is if you add anything to your pivot table
and the columns for the data change. If it remains the same, you should be
good to go.

 

[ShaneDevenshire]

Hi,

Assuming you are using 2003 or earlier -
1. You can make both of those calculations in the pivot table at the same
time you do a count.
a. Drag the field you a using from the Pivot Table Field List onto the Data
area of the pivot table two more times.
b. Select one of the new fields and change its calculation to Average using
the Field Settings button. Change the calculation for the other field to
Standard Deviation.

2. You can create formulas outside the pivot table that "track" the pivot
table range as you modify the pivot table. To do this the Generate GetPivot
Data button must be turned on. However, this button is not displayed on the
default pivot table toolbar. To display it choose View, Toolbars, Customize.
Pick the Commands tab and the Data catergory. Search way down on the list
and drag the above mentioned button to the toolbar.
Now when you create your formulas with this button active, if you highlight
with the mouse, don't type the cell addresses, Excel will create the
GetPivotData function for you.

 

[jesoldano]

YOu could create a dynamic range to do it and then calculate

=STDEV(myRange)

Take a look here for instructions on setting up the dynamic range.

http://www.contextures.com/xlNames02.html

The only problem I can foresee is if you add anything to your pivot table
and the columns for the data change.   If it remains the same, you should be
good to go.
--
HTH,
Barb Reinhardt





- Show quoted text -

it worked! Thanks very much! Do you know how I can ensure that, when
new records are added to my source document, that the new records are
captured in the pivot table when it's refreshed? Currently, I can
update the pivot table successfully if I delete records from the
source document, but not if I add records. Thank you for all your
help.