Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table question
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table question
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 " wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table question
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. -- Cheers, Shane Devenshire " wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table question
On Sep 20, 10:36*am, Barb Reinhardt
wrote: 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 " wrote: 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!- Hide quoted text - - 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table question | Excel Worksheet Functions | |||
Pivot Table Question | Excel Discussion (Misc queries) | |||
Pivot table question | Excel Discussion (Misc queries) | |||
Pivot Table Question : If statment in Pivot Table?? | Excel Discussion (Misc queries) | |||
Pivot Table Question | Excel Discussion (Misc queries) |