Pivot Table, formulae and calculated fields 1

[anationalacrobat]

My pivot table looks like this:

class jan feb march april
english 5 6 7 5
math 3 2 4 5
geography 4 6 2 8

I'd like to calculate average attendance.

Now the easy-peasy way is to just stick =average(range) to the right and highlight jan to april for that row. Of course, this will get overwritten the moment a may value is entered into the underlying data and a new column is required for the pivot table. So I'd like to do it the smart way.

I'm supposed to be able to put calculated fields in here but it seems like they only want to go for each month -- if I try to stick in an average of the attendance, I get an average of the attendance of the days within the month whereas I want it to be an average of the monthly totals.

I must be missing something obvious here. What is it? HALP!

 

[GlennUK]

Now the easy-peasy way is to just stick =average(range) to the right ...

... or you could put it to the left of the pivot-table, and it wouldn't get overwritten ( i.e. insert a column to the left of your pivot-table ). Make sure you cover a range wide enough to include future months.

Is that suitable?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[anationalacrobat]

Yes, I can certainly put it on the left, though most people would expect that sort of thing to appear on the right. I did a lot of other research after posting this question to see if there was an answer to be had. Amazingly, it appears there's no good solution, only hacks!

I can't understand it. Pivot tables are full of awesome except for these stupid, glaring omissions that have been an issue since Excel 97. In ten years worth of updates Microsoft couldn't think to add things like distinct counts? Ugh! So obvious and so lacking!