pivot tables, calculated fields, empty cells and zeros

G

Guest

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
 
M

MartinW

Hi GPO,

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

HTH
Martin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

pivot tables, calculated fields, empty cells and zeros 1
Replace (blank) entry in a pivot report with clear cell 1
VLookUp, empty cells, and zeros 4
Format cells to make zeros not appear,or be invisible on screen &p 3
Enter value zero into all cells in multiple ranges? 4
Pivot tables - calculated fields and items 5
pivot table calculated field 1
Zeros in empty merge fields 1

Top