pivot tables, calculated fields, empty cells and zeros

G

GPO

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
Persistent "Formula refers to empty cells" error despite cells evidently NOT being em 2
Slicers and Calculated Items in Pivot Table 2
Stop cell showing zero when returning a blank cell 2
A data validation question 1
Two formulas in one cell? 4
Enter value zero into all cells in multiple ranges? 4
Adding recognised Zeros 5

Top