Remove Zeros from Pivot Table Averages

[Jason Smith]

Hello All,

This is my first post, so any answers would be greatly appreciated. I need to know if there is a way to remove the zeros from my averages inside a pivot table.
Ex: I have 10 people on a team, but only 5 have data, the other 5 have zeros. I want have the average in the pivot table to reflect only the five with data, and not the other 5. Currently it is averaging all 10 people and skewing my numbers.

Any ideas?

Thanks!

 

[Fazza]

Welcome, Jason. Yes, there are ways.

If you start from the pivot table wizard with the external data option and go into MS Query a criteria can be applied there to exclude the zero entries, or maybe better to make them nulls. If you get a message about no visible tables OK to that and then under 'options' select 'system tables' to see the worksheet. I can be more specific if you required - please give some further information on the set up of your data and what you are doing. You might use something like "WHERE field_name<>0" or use "Iif(field_name = 0,Null, field_name) etc".

HTH, Fazza

 

[Jason Smith]

Thanks Fazza, Great info. I was actually able to modify my SQL query a bit to make this work, before bringing it into Excel. I am learning my way around the Pivot Tables in 2007 and your info will definitely help me out in the long run.

Jason

 

[Fazza]

Well done, Jason. Regards, F