Pivot Table Blank Cells

[DDAZET]

I have a pivot table that compares year-on-year sales data for accounts. If an account didn't have sales for the earlier year, the custom calculation errors #DIV/0! which I can show as <blank> at the account level. Problem is at the subtotal levels, I also get the error when I should be getting a value for the group. The error for the one account is affecting my subtotal.

Is there a way I can Show Empty Cells as a VALUE instead of TEXT of say... 0.001 so I can eliminate the error messages?

 

[texasalynn]

Welcome to MrExcel board.

I found this so see if it helps - I haven't checked it out
"If you look at the pivot table options properties, you should see a check box that says "For Error Values Show" and then a textbox that you can put in what you want to show if there is a error."

 

[DDAZET]

Thanks texasalynn. I can Show Errors as <blank> at the row level. My problem is when I subtotal the column that has an error in it, the subtotal errors out also. So what I need to do is create a false calculation on the blank cells to eliminate the errors on the rows and allow my subtotal to correctly calculate.

 

[DDAZET]

I meant to say: I can Show Errors as "Blank" <BLANK>at the row level

 

[texasalynn]

take a look at this
http://www.juiceanalytics.com/writing/excel-using-pivottables-for-reporting/

 

[DDAZET]

Thanks again, texasalynn.

The solution you suggested is not usable. My problem is when I subtotal the column that has an error in it (blank or otherwise), the SUBTOTAL errors out also where there should be a value.

What I want to do is replace Empty Cells with a miniscule VALUE to totally eliminate the errors so that my Subtotal will calculate correctly.
<!-- / message -->

 

[texasalynn]

did you not see in the properties example there is a place to also but in the value for blanks?????

 

[DDAZET]

Yes, I've used that property before. However, whatever I put in the comes through to the table as TEXT not as a VALUE. Text doesn't calculate. I need it to be a true value.

 

[texasalynn]

I did a google search and find a youtube
http://www.youtube.com/watch?v=uBU0F_bElXc&feature=related
but can't view it at work

 

[DDAZET]

Nevermind. I figured out how to eliminate the error messages in my pivot table. Errors occured in my Calculated Field for my variance,
(Actual-Plan)/Plan, when Plan = 0. I inserted two new columns in my data called Numerator & Denomination.

Numerator: if (Actual-Plan = 0, then 0.0001, else Actual-Plan)
Denominator: if ( And (Actual-0,Plan=0), then 0.0001, else if (Plan=0, then Actual, else Plan)) Result = no zeros

Calculated Field: Numerator / Denominator