Creating an Average that skips a row

[patriciaanne]

I am way over my head on this part.
Every month I am creating a pivot table and then splitting it into sheets by the report filter. So, the rows and columns on every sheet change every month. The last row of each table and sheet, says Grand Total so my macro is searching for Grand Total and then going down a row, typing the word average and then moving over to the next column.
What I need help with is - I need it to average the column but exclude the Grand Total adn then do the same thing for the next columns...etc and then do it for the other sheets.

The whole average part is not working ... the code is below.
y = Worksheets.Count - 2
For Z = 1 To y
Worksheets(Z).Select
Columns("A:A").Select
Cells.Find("Grand Total").Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Average"
ActiveCell.Offset(0, 3).Select
Selection.Offset(-1, 0).Select
No = ActiveCell.Value
Selection.Offset(1, 0).Select
Avg = Average(No)
ActiveCell.Value = Avg
For x = 1 To 8
Selection.Offset(0, 1).Select
Selection.Offset(-1, 0).Select
No = ActiveCell.Value
Selection.Offset(1, 0).Select
Avg = Average(No)
ActiveCell.Value = Avg
Next x
Next Z

Any help is appreciated.. Thanks



To everything there is a season, a time for every purpose under the sun.

 

[SkipVought]

Hi,

You would be much, much better off, doing ANY aggregation, using the Source Data, rather than on a report, ESPECIALLY when it comes to averages.

This can most likey be accomplished, using spreadsheet functions, like the SUMPRODUCT, where you can include multiple criteria and either SUM or COUNT, both of which you'll need to get an average.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[patriciaanne]

can you have both a grandtotal and a grand average on the main pivot table so that when I split the pivot table by salesperson in the report filter both the grandtotal and grand average would both show up on each sheet?

To everything there is a season, a time for every purpose under the sun.

 

[SkipVought]

What happens if you try that?

The great thing about PivotTables is that its so easy to do "what if" and try stuff!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[patriciaanne]

it did what I thought it would..since it is not in the pivot table, it didn't take it over.... The pivot table lets you summarize by total or average but apparently it only accepts one or the other not both... guess I'll see if it works as a calculated field maybe...

To everything there is a season, a time for every purpose under the sun.

 

[SkipVought]

"...accepts one or the other not both."

Not true!

Drag you field into the DATA area AGAIN!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[patriciaanne]

Skip, What you said is true but it creates a column of averages.
All I want is a Grand Total and a Grand Average at the bottom of the pivot table.. summing and averaging the columns.

To everything there is a season, a time for every purpose under the sun.

 

[SkipVought]

So add another PT below the existing one (can be a bit dangerous) that just TOTALS (no ROW fields)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[patriciaanne]

Thanks but I figured another way although it is not totally automated. Macro searches for Grand Total, then macro has an input box that asks number of salespeople (it varies by dept sheet) and it divides that by the pivot grand total.
It works and I am done...Yeah

To everything there is a season, a time for every purpose under the sun.

 

[Gavona]

then splitting it into sheets by the report filter.

I assume you mean that you use the ShowPages command. If not then you should!

Skip, What you said is true but it creates a column of averages.
All I want is a Grand Total and a Grand Average at the bottom of the pivot table.. summing and averaging the columns.

Why not use Skip's approach then use autofilter to hide the rows you are not interested in.
Assuming column D contains "Sum of xxxx" or "Average of "xxxx":
Highlight column C
Data, Filter,Autofilter
then choose custom criteria "Does not begin with" Average

The above will leave you with averages for any sub-totals within your pivot table but applying the same sort of technique to other columns could eliminate them as well if desired.

OOPS - you wanted code - record doing the above and you get something like

    Columns("D:D").AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="<>Average*", Operator:=xlAnd

Gavin