Pivot Table Custom Calculation displays #NULL!

[ssgmiami]

I have a pivot table that tracks Rents Billed by property, year and month. The rows contain the Property ID and Year, the columns contain the Month. There is a custom calculation to show the % Increase of difference from previous year and month. The calculation works fine for all the years except the current year. For the current year, the calculation for any past months is correct, but for future months the calculation produces a #NULL! result. This is because there are no rent billings yet for those months causing the custom calculation to be invalid.

For the % Inc custom calculation:
Show Value as = % Difference From
Base Field = Year
Base Item = (previous)

Is there a way change the custom calculation or to remove the #NULL! using either formatting or conditional formatting?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
7		January		February		March		April		May		June		July
8	Row Labels	Billed	% Inc	Billed	% Inc	Billed	% Inc	Billed	% Inc	Billed	% Inc	Billed	% Inc	Billed	% Inc
9	2500 INVERRARY
10	2012	$316,059		$320,893		$340,149		$343,795		$346,900		$333,745		$329,695
11	2013	$334,757	5.92%	$344,770	7.44%	$363,954	7.00%	$356,491	3.69%	$360,938	4.05%	$353,155	5.82%	$350,459	6.30%
12	2014	$359,780	7.47%	$354,762	2.90%	$355,790	-2.24%	$370,018	3.79%	$375,338	3.99%	$367,033	3.93%	$367,721	4.93%
13	2015	$383,520	6.60%	$372,977	5.13%		#NULL!		#NULL!		#NULL!		#NULL!		#NULL!
14
15	441 INVERRARY
16	2012	$228,914		$228,320		$232,846		$239,569		$249,713		$241,775		$237,137
17	2013	$244,325	6.73%	$242,633	6.27%	$243,147	4.42%	$244,200	1.93%	$243,886	-2.33%	$245,350	1.48%	$240,058	1.23%
18	2014	$248,480	1.70%	$252,710	4.15%	$254,788	4.79%	$258,965	6.05%	$257,150	5.44%	$258,672	5.43%	$259,715	8.19%
19	2015	$270,643	8.92%	$271,926	7.60%		#NULL!		#NULL!		#NULL!		#NULL!		#NULL!
20
Pivot-Property

 

[Asala42]

By direct formula, you could wrap an IFERROR around your formula. For example:

=IFERROR(CurrentFormula,"")

Conditional formatting could also do it, using ISERROR(ref) as your Conditional format logic.

 

[ssgmiami]

I'm not having any luck with Conditional Formatting to solve the problem. Since this is a Pivot table custom calculation I have no access to the formula and cannot wrap it in an IFERROR. I am sure that conditional formatting is the answer, but I cannot seem to get the formula correct. Here is what I am doing.

1. I have selected a cell within the % Inc column, in this case $G$13
2. I select Conditional formatting - New Rule
3. In the rule I make the following entries:
Apply Rule to - All cells showing "% Inc" values
Rule Type - Use formula to determine which cells to format
Format values where this formula is true - =ISERROR($G$13)
Format = ;;;

This does not work, I am at a loss as to what do do. I cannot submit the report showing #NULL!'s. Any ideas? Any help would be greatly appreciated

 

[Asala42]

Oh you know it occurs to me there's an easier solution.

Right click on the pivot table -> Pivot Table Options ->check the box for "For error values show".
That will remove all error values from your pivot

 

[ssgmiami]

Perfect!!! Can't believe that the answer was so simple. I have seen that option a thousand times and it never occurred to me that I would need it.

Your assistance is most appreciated!