I'm having an issue with a pivot table giving me "#DIV/0!" because it is taking the average of a number and a blank cell. How can I have the pivot table take the average of desired values but ignore the blank cells? Team Program Completion NorthA North NorthB North 40% NorthC North 85% SouthA South 6% SouthB South 14% This is an example of my data and my pivot table shows the program and average completion. Instead of taking the average of 40% and 85% North, it give me an error. HELP!

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Pivot Table Averages - Ignore Blanks?

Thread starter ArielLeigh
Start date Mar 13, 2019
Tags

average error in pivot north pivot pivots

ArielLeigh

New Member

Mar 13, 2019

I'm having an issue with a pivot table giving me "#DIV/0!" because it is taking the average of a number and a blank cell. How can I have the pivot table take the average of desired values but ignore the blank cells?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Team[/TD]
[TD]Program[/TD]
[TD]Completion[/TD]
[/TR]
[TR]
[TD]NorthA[/TD]
[TD]North[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NorthB[/TD]
[TD]North[/TD]
[TD]40%[/TD]
[/TR]
[TR]
[TD]NorthC[/TD]
[TD]North[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]SouthA[/TD]
[TD]South[/TD]
[TD]6%[/TD]
[/TR]
[TR]
[TD]SouthB[/TD]
[TD]South[/TD]
[TD]14%[/TD]
[/TR]
</tbody>[/TABLE]

This is an example of my data and my pivot table shows the program and average completion. Instead of taking the average of 40% and 85% North, it give me an error. HELP!