# Posts by jdowski

• ## Pivot Table running averages

My question is two part.

Part I:
Can you, in a pivot table, create "running averages"? I have a table where I am summing dollar amount data by officer, & by region, measured by month end date. If I pull in a second time the data field which I'm already summing and choose average, I get the average of the dollar amounts that make up the total. I want the totals averaged by month, both by officer and by region if possible. I had been doing this with a large table of sumproduct formulas except that if a new officer was added, their data would not flow into my table until I added a line for the new officer. Otherwise my table would be off by the amount of the unidentified officer's totals. Pivot tables automatically bring in all the data thereby eliminating my having to check for new officers each month. However, now I have to figure out a way to calculate monthly averages.....

Part II:
If Part I can't be done, then can I use the GETPIVOTDATA function to extract either column or row totals that are automatically inserted in a pivot table? I have played with it a little and thus far have not been able to get it to work.

Thanks,

It seems pivot table questions don't get a lot of response here on the board. I guess in part due to there not being as widely used as many other Excel features.....

Joe Dowski
Connecticut, USA.

• ## Dynamic Named Range that expands across, not down ??

Hi All,

I know how to write the standard dynamic range that expands down:
=offset(\$A\$1,0,0,counta(\$A:\$A))...
But I want my range to expand across and not down, I tried
=offset(\$A\$1,0,0,,counta(\$1:\$1)) but it's not working ????

Any thoughts on this??

Puzzled in Connecticut...

Joe

• ## Calculated Field in PivotTable

Hi All,

My first post to this BB. I am just starting to play with pivottables...til now have mostly done my summarizing with SumProduct formulas but here goes...
I have a pivottable that summarizes data by region (7 regions). I have created a calculated field that divides the delinquency number into the total delinquency number, however, I want this number only at the total level, not the regional level. Can I somehow hide the regional totals while still displaying the summary total?