I am trying to create a calculated Weighted Average field in an Excel Pivot Table that does NOT include zeroes in the "Total" average row at the bottom of that specific section. I understand how to make the weighted average, but I cannot figure out how to exclude zeroes. I have changed blanks to nas and back again, and every time they result in zero.
First of all, here is the Pivot Table that I have right now: Current Pivot
Notice how the 0s in this column are resulting in an average of 1,397, when really the average there should be 12,719. This is my problem. How do I get THAT field to show me 12,719 instead of 1397?
What I want is the fields that say 0 to show - or be blank, or at the very least not have the 0 be calculated into the Total row.
I even re-wrote this calculated field to give me "na" instead of 0 using the formula here:
=if((weighted avg formula)=0,"na",(weight avg formula)
This worked and resulted in nas in the pivot, but they were treated as 0s.
My source data has only numbers greater than 0 or "na". I also tried replacing the nas with blanks, and it still results in the same 1397 average.
I also came across this Stack Overflow article which discusses ErrorStrings and that looks like it MIGHT solve my problem, but I have no idea how to implement that PivotTable object property. I'm somewhat familiar with VBA within Excel, but really only on a Macro-writing basis. How to add a weighted average in my pivot table?
