title: Excluding zeroes in a weighted average within a Calculated Field of a Pivot Table
body: <p>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. </p>
  
  <p>First of all, here is the Pivot Table that I have right now:
  <a href="https://i.stack.imgur.com/hrxJN.png" rel="nofollow noreferrer">Current Pivot</a></p>
  
  <p>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?</p>
  
  <p>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.</p>
  
  <p>I even re-wrote this calculated field to give me "na" instead of 0 using the formula here:</p>
  
  <pre><code>=if((weighted avg formula)=0,"na",(weight avg formula)
  </code></pre>
  
  <p>This worked and resulted in nas in the pivot, but they were treated as 0s.</p>
  
  <p>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. </p>
  
  <p>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.
  <a href="https://stackoverflow.com/questions/17550209/how-to-add-a-weighted-average-in-my-pivot-table">How to add a weighted average in my pivot table?</a></p>