davidnz
asked on
Count rows in a pivot table
Hi,
I have a pivot table with a page field in Excel 2000. I want to get the average of the data, but have this shown outside the pivot table.
I can get the grand total of the data by using the function "getpivotdata", but how do I get the count of the rows? The number of rows change depending on the page field selected so a named range won't work.
Any ideas?
Thanks.
I have a pivot table with a page field in Excel 2000. I want to get the average of the data, but have this shown outside the pivot table.
I can get the grand total of the data by using the function "getpivotdata", but how do I get the count of the rows? The number of rows change depending on the page field selected so a named range won't work.
Any ideas?
Thanks.
ASKER
Thanks
But I don't want to run a macro each time, is there not a function I can use or add a count item to the pivot table?
Cheers.
But I don't want to run a macro each time, is there not a function I can use or add a count item to the pivot table?
Cheers.
Do currently create the pivot table with code?
ASKER
no i don't.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
davidnz,
There is an option to add average to the pivot table itself. Enter the pivot table wizard and enter the column that you want to average in the data area. Don't worry that you already have something that says Sum or Count of the same item already. This will add another item to your data area in the default format of the program (mine enters "Sum of Junk" for example). Double-click on this item and change the "Summarize by" option to Average, click on Finish. This will add an Average value to your table.
Now when you create or refresh your pivot table data you will have an average item that will adjust to the table automatically. You can set your table to display only this item, then copy and paste special items as needed thereafter.
Or you could create a new table based on the old one with just the average in it and then paste special the data as needed.
Hope this helps.
There is an option to add average to the pivot table itself. Enter the pivot table wizard and enter the column that you want to average in the data area. Don't worry that you already have something that says Sum or Count of the same item already. This will add another item to your data area in the default format of the program (mine enters "Sum of Junk" for example). Double-click on this item and change the "Summarize by" option to Average, click on Finish. This will add an Average value to your table.
Now when you create or refresh your pivot table data you will have an average item that will adjust to the table automatically. You can set your table to display only this item, then copy and paste special items as needed thereafter.
Or you could create a new table based on the old one with just the average in it and then paste special the data as needed.
Hope this helps.
ASKER
I knew there must be an easy way! Thanks.
Hi, again.
I'm just curious - I don't mean to be ungrateful...
What made you decide to select my suggestion instead of bkpchs237's?
Bye. -e2
I'm just curious - I don't mean to be ungrateful...
What made you decide to select my suggestion instead of bkpchs237's?
Bye. -e2
ASKER
2 reasons:
I wanted the formula outside the pivot table as stated in my original question.
I have a number of items to average - so his/her way would mean more work.
Thanks to everyone for responding.
I wanted the formula outside the pivot table as stated in my original question.
I have a number of items to average - so his/her way would mean more work.
Thanks to everyone for responding.
Sub DetermineUsedRange(ByRef theRng As Range)
Dim FirstRow As Integer, FirstCol As Integer, _
LastRow As Integer, LastCol As Integer
On Error GoTo handleError
FirstRow = Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows).Row
FirstCol = Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByColumns).
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPreviou
SearchOrder:=xlByRows).Row
LastCol = Cells.Find(What:="*", _
SearchDirection:=xlPreviou
SearchOrder:=xlByColumns).
Set theRng = Range(Cells(FirstRow, FirstCol), _
Cells(LastRow, LastCol))
handleError:
End Sub