View original (French)

Insert an average field in a pivot table

Vdj94 Posted messages 7 Status Membre -  
Ant049 Posted messages 157 Status Membre -
Hello,

I created a pivot table to analyze monthly data over several years and I would like to get the average of this data.

I would therefore like to insert a column that would calculate the average of the data in each row of my pivot table.

Here is an example of my spreadsheet:
http://www.cjoint.com/c/FHsmitNgDeT

Thank you in advance,

Vdj94

PS: By the way, I don't understand why, even having checked the row total box, I only get the grand total on my last line and not also a total in the column...

1 réponse

Réponse 1 / 1
Ant049 Posted messages 157 Status Membre 22
 
Hello,

https://www.lecfomasque.com/excel-calculer-une-moyenne-dans-un-champ-personnalise-dun-tableau-croise-dynamique/

--
When the wise points to the moon, the fool looks at the finger.
3
Vdj94 Posted messages 7 Status Membre
 
Thank you very much, Ant049!
0
Vdj94 Posted messages 7 Status Membre
 
The formula does indeed work.

However, I am faced with the following problem:
In cases where the range has zeros at the beginning of the year because we start the activity in the middle of the year for example, the formula returns an average that takes into account the zeros at the beginning of the year.

How can we calculate the average of the data starting only from the moment the first non-zero number appears?

Thank you in advance,

Vdj94
0
Ant049 Posted messages 157 Status Membre 22
 
Try this and get back to me if it doesn't solve the problem:


Hide zero values in a pivot table report

- Click on the pivot table report.

- In the Analyze tab, in the PivotTable group, click the arrow next to Options, then click Options.

- Select the Layout & Format tab, then perform one or more of the following actions:

- Change the error display mode Under Format, check the box for For error values, show. In the box, type the value to display instead of errors. To display empty cells instead of errors, delete any characters in the box.

- Change the display mode for empty cells Check the box for For empty cells, show. In the box, type the value to display in empty cells. To display empty cells, delete any characters in the box. To display zeros, uncheck the box.
0