?
Solved

Count rows in a pivot table

Posted on 2001-07-04
9
Medium Priority
?
2,571 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:davidnz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 6

Expert Comment

by:xSinbad
ID: 6254117
Here is a good sub that you can modify to your own needs;


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).Column
LastRow = Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row
LastCol = Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column
Set theRng = Range(Cells(FirstRow, FirstCol), _
    Cells(LastRow, LastCol))
handleError:
End Sub
0
 

Author Comment

by:davidnz
ID: 6254184
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.
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6254196
Do currently create the pivot table with code?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:davidnz
ID: 6254206
no i don't.
0
 
LVL 7

Accepted Solution

by:
q2eddie earned 400 total points
ID: 6256059
Hi, davidnz.

#Try This
This assumes that your pivottable's data column starts at B5.

=GETPIVOTDATA(B5,"grand total")/(COUNTA(B5:B10000)-1)

Subtract "1":
one for the data column's grand total.

or...

If you don't know how many items are going to be in the list, then use this code.

=GETPIVOTDATA(B5,"grand total")/(COUNTA(B:B)-3)

Subtract "3":
one for the page field's combobox.
one for the data column's title.
one for the data column's grand total.

Bye. -e2
0
 
LVL 6

Expert Comment

by:bkpchs237
ID: 6256398
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.
0
 

Author Comment

by:davidnz
ID: 6257337
I knew there must be an easy way!  Thanks.
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6257425
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
0
 

Author Comment

by:davidnz
ID: 6257450
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.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question