Average of Pivot Table values (refreshable)

[JuJuBe]

I have a pivot table that is drawn from data in a spreadsheet that will be set up with a conenction to a data source and be refreshed every so often. There are a few columns of formulas on the right hand side of the refreshable data that include =weekday, =month,=choose(A2,"Sunday",Monday".... etc to give me some of the fields used in the pivot. The pivot table has Month Name and Day Name, (March and Sunday for example), in the Rows, Weeknumber in the columns, and count of transactions in the values.

What I want is to have a summary below the pivot table that gives me the average of transactions by weekday and the % of total by weekday. This summary will feed 2 graphs on seperate workbook tabs. It should look like this:



Day Rx's %
Sunday 5144 5.9%
Monday 17282 18.8%
Tuesday 15713 17.7%
Wednesday 15885 18.2%
Thursday 15301 19.4%
Friday 13155 15.2%
Saturday 4157 4.8%

Right now the formulas for these are =Average(cell1,cell2,cell3...) but as the pivot table is refreshed and expanded the formulas won't grow to accomidate the added data.

I was trying to do a "=average(count(index(match" type of formula but I got lost. Please help....
I tried to upload a .zip "example" file but I kept getting "upload of file failed". It is well below the 9MB size limit.

[NBVC]

Maybe?

Leave at lease one blank row between table and your first formula then use a range in the formula that covers from first cell in pivot table to cell just above your formula...

e.g... =AVERAGE(A1:A100)

so when table refreshes and everything is pushed down, it should still incapsulate your range of values.

[JuJuBe]

I was able to make the example pivot small enough to upload, see attached.

Since there is no source data in this example I think as long as you don't refresh it it will still display values. I think with pivot tables that they will not push data below or to the side of the pivot but insterad will paste itself over it. Is there a setting to change this? If not I will have problems.

NBVC, Since there are all of the day names in the same column I can't just do an average of A1:A100, I need to parse out the daily totals based on day name; "Sunday", "Monday", etc...

[NBVC]

tRY:

=AVERAGEIF($A$3:$A$33,B35,$P$3:$P$33)

AND

=AVERAGEIF($A$3:$A$33,B35,$Q$3:$Q$33)

[JuJuBe]

That worked, I didn't know about averageif, pretty nifty.

The problem however is that because the ranges are absolute they will not capture the new range when the data is refreshed and new months and new week #'s are added. I think I almost need a =count(cell or something to look for the words "Grand Total" in column A and in row 6 to know where the criteria range and average range should stop.

Also, as I suspected the pivot will write over values below or next to the pivot table when refreshing the data. Hopefully there is a setting to change this.

[NBVC]

I may have been wrong.. I think it will overwrite your information below as table expands.. can't find a feature to change that... you may need to put it to the side...

to change formula to be dynamic based on Grand Tota:

=AVERAGEIF($A$3:INDEX($A:$A,MATCH("Grand Total",$A:$A,0)),B35,$P$3:INDEX($P:$P,MATCH("Grand Total",$A:$A,0)))

[JuJuBe]

Instead of using column $P:$P is there a way to have it search out the last column that contains Grand Total? As the pivot grows it will add new week numbers, (column headers), up to week 52.

Thanks

[NBVC]

Try:

Please Login or Register  to view this content.

I went up to 1000 rows possible.. .but you can go lower if you don't think it would reach that big....