pivot tables, calculated fields, empty cells and zeros
 

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

"GPO" wrote in message
...
Excel 2000.

I have a calculated field in my pivot table that simply divides one field
by
another.

The formula is =my_numerator/my_denominator.

The underlying data in the numerator field can legitimately be
a) an integer greater than or equal to zero, or
b) an empty cell. The empty cell and zero are NOT the same thing.

What I want is:
a) When my_numerator is zero and is divided by my_denominator, a zero is
returned.
b) When my_numerator is the empty cell and is divided by my_denominator,
an
empty cell is returned.

At the moment, when my_numerator is the empty cell and is divided by
my_denominator, a zero is returned, which is incorrect.

What can I do?

Regards

GPO

pivot tables, calculated fields, empty cells and zeros
 

On Oct 20, 5:17 pm, GPO wrote:
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

"GPO" wrote in message

...

Excel 2000.

I have a calculated field in my pivot table that simply divides one field
by
another.

The formula is =my_numerator/my_denominator.

The underlying data in the numerator field can legitimately be
a) an integer greater than or equal to zero, or
b) an empty cell. The empty cell and zero are NOT the same thing.

What I want is:
a) When my_numerator is zero and is divided by my_denominator, a zero is
returned.
b) When my_numerator is the empty cell and is divided by my_denominator,
an
empty cell is returned.

At the moment, when my_numerator is the empty cell and is divided by
my_denominator, a zero is returned, which is incorrect.

What can I do?

Regards

GPO

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_denominator0, 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.