Solved

Count and average in a pivot?

Posted on 2010-09-22
14
787 Views
Last Modified: 2012-08-14
Hello experts,

In excel 2007, how can I make dynamic count and average in rows and columns in a pivot table?

See the attached example

vba is ok - as long as it's as dynamic as the pivot

Regards, Raahaugen Mappe1.xls Mappe1.xls
0
Comment
Question by:Raahaugen
  • 8
  • 4
  • 2
14 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33735996
You can't make them part of the pivot, so you need formulas. Add this code to the worksheet (after removing the other pivottable)
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Dim rngData As Range

    Dim lngRows As Long, lngCols As Long, lngRow As Long, lngCol As Long

    Set rngData = Target.DataBodyRange

    With rngData

        lngRow = .Row

        lngCol = .Column

        lngRows = .Rows.Count

        lngCols = .Columns.Count

        With .Offset(lngRows).Resize(1, lngCols - 1)

            .FormulaR1C1 = "=COUNT(R" & lngRow & "C:R[-2]C)"

            .Offset(1).FormulaR1C1 = "=average(R" & lngRow & "C:R[-3]C)"

            .Offset(, lngCols - 1).Resize(, 1).FormulaR1C1 = "=sum(RC" & lngCol & ":RC[-1])"

            .Offset(1, lngCols - 1).Resize(, 1).FormulaR1C1 = "=average(RC" & lngCol & ":RC[-1])"

        End With

        With .Offset(, lngCols).Resize(lngRows - 1, 1)

            .FormulaR1C1 = "=COUNT(RC" & lngCol & ":RC[-2])"

            .Offset(, 1).FormulaR1C1 = "=average(RC" & lngCol & ":RC[-3])"

        End With

    End With

        

End Sub

Open in new window

0
 

Author Comment

by:Raahaugen
ID: 33736428
Great - is it possible to extend the code to clear the unused fields after update? let's say first the pivot shows 4 dates, and it counts just fine. Then instead I want to look at only 3 dates, this makes the pivot 1 column smaller, and thus the last column with the average from before tableUpdate is still standing, though it is no longer relevant?

But great work really.. in the workbook where this will be used, I have both many more fields as well as rows of data to calculate from, so I hope to make this work efficiently - not least because many people will be using it.

Regards
0
 

Author Comment

by:Raahaugen
ID: 33736446
Oh and one more thing - the average shows many decimals. Is it possible from vba as well to make the cells show only 2 decimals?
0
 

Author Comment

by:Raahaugen
ID: 33736495
I will be playing around with some background colors as well, so please explain to me where I can set cell specifications on the rows and columns listing count and average?

By specifications I mean colors, format and so on.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 33739039
Try this:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim rngData As Range
    Dim rngColSummary As Range, rngRowSummary As Range
    Dim lngRows As Long, lngCols As Long, lngRow As Long, lngCol As Long
    Set rngData = Target.DataBodyRange
    
    With rngData
        lngRow = .Row
        lngCol = .Column
        lngRows = .Rows.Count
        lngCols = .Columns.Count
        ' this is the first row of the summary under the table
        Set rngColSummary = .Offset(lngRows).Resize(1, lngCols - 1)
        Set rngRowSummary = .Offset(, lngCols).Resize(lngRows - 1, 1)
    End With
    
    ' clear rest of sheet
    ' all rows below table
    Range(Cells(lngRow + lngRows, "A"), Cells.SpecialCells(xlCellTypeLastCell)).Clear
    ' then columns to the right
    Range(Cells(1, lngCol + lngCols), Cells(1, Columns.Count)).EntireColumn.Clear
    
    
    With rngColSummary
        ' colour both summary rows
        ' need to extend by one column to include the grand totals bit
        .Resize(2, .Columns.Count + 1).Interior.ColorIndex = 3 ' red
        ' add count formula
        .FormulaR1C1 = "=COUNT(R" & lngRow & "C:R[-2]C)"
        ' add average formula in row below and set number format
        With .Offset(1)
            .FormulaR1C1 = "=average(R" & lngRow & "C:R[-3]C)"
            .NumberFormat = "0.00"
        End With
        ' add grand totals in column to right
        .Offset(, lngCols - 1).Resize(, 1).FormulaR1C1 = "=sum(RC" & lngCol & ":RC[-1])"
        .Offset(1, lngCols - 1).Resize(, 1).FormulaR1C1 = "=average(RC" & lngCol & ":RC[-1])"
    End With
    
    With rngRowSummary
        'Add colour to both columns
        .Resize(, 2).Interior.ColorIndex = 5
        
        .FormulaR1C1 = "=COUNT(RC" & lngCol & ":RC[-2])"
        With .Offset(, 1)
            .FormulaR1C1 = "=average(RC" & lngCol & ":RC[-3])"
            .NumberFormat = "0.00"
        End With
    End With
        
End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:gbanik
ID: 33739087
Here is a fun-n-learn version of your file. Well ! it does what you ask for... ;)

ps. If you are keen to proceed with this kind of a solution, you should be able to dynamiclly populate the formulaes and achieve your objective. It is possible.
Mappe1.xls
0
 

Author Comment

by:Raahaugen
ID: 33741362
Rorya - that is exactly what I'm talking about!!!

Just one more thing: Whenever I add one date (makes the pivot wider or taller), excel keeps asking me, if I want to replace the content of the destination cells? How can I remove that pop-up every time?

The code does exactly what it should!

gbanik, your formula works, but doesn't fulfill my wish. To make it do that, the formula will have to dynamically move with the pivot table.

the best -
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33741739
You can't unless you are using code to cause the refresh (in which case you can set Application.DisplayAlerts = False)
0
 

Author Comment

by:Raahaugen
ID: 33746127
I see - perfect! Full points

gbanik I will give you a chance to post something that works - not that you have to! But if you want the points, you are welcome.

Tomorrow I will close the question.

Rorya, you're the best !

Cheers
0
 
LVL 13

Expert Comment

by:gbanik
ID: 33899423
Raahaugen... please go ahead and close the question. Though it is possible to do what I suggest... but may not be worth the effort. Rorya's solution is good for you.
0
 

Author Closing Comment

by:Raahaugen
ID: 33901467
As said, this sulution simply works. Sorry I didn't close the q earlier.

Thanks for reminding.

Regards, raahaugen
0
 

Author Comment

by:Raahaugen
ID: 33950586
Hey rorya!

Please help me rewrite the code to only write the summary under the pivot, and don't write no summary to the right?

The case is, I have a pivot with only one column, and then underneath the column I want the count and the average number.

Regards, Raahaugen
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33950925
That would be a new question... :)
0
 

Author Comment

by:Raahaugen
ID: 33951418
Roger!

:-)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now