pivot tables, calculated fields, empty cells and zeros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks Martin, but that didn't work. Outside of the pivot table excel sees
the cell as blank, but when a formula is applied in a pivot table, it is
treating empty cells as zeros. Try it.

Regards

GPO
/*******************************/

Hi GPO,

ISBLANK should fix that for you, something like
=IF(ISBLANK(my_numerator),"",my_numerator/my_denominator)

HTH
Martin
 
Thanks Martin, but that didn't work. Outside of the pivot table excel sees
the cell as blank, but when a formula is applied in a pivot table, it is
treating empty cells as zeros. Try it.

Regards

GPO
/*******************************/

Hi GPO,

ISBLANK should fix that for you, something like
=IF(ISBLANK(my_numerator),"",my_numerator/my_denominator)

HTH
Martin
Click to expand...

Hi GPO.
I ran into a similar issue and here's how I fixed it. In my
pivot, where the numerator was 0, I wanted the calculated field to be
0, but when the denominator was 0 as well, I wanted the cell to be
blank. I had to do two things to fix the issue. First, I used the
following formula:

IF(AND(my_denominator>0, my_numerator=0),0,my_numerator/
my_denominator)

This gave me a zero for the cells I wanted to show zeros in and a
#DIV/0! error in the cells that I wanted to be blank. I then right-
clicked on the Pivot table to open up Pivot Table Options. On the
first page, under the Format section, I checked the box that says "For
error values show:" and left it blank. That blanked out all of the
#DIV/0!'s. That seemed to fix my issue. I hope this helps you with
yours.
 
Back
Top