Calculated Fields showing error results

P

Pasko1

I am trying to get a calculated field to work within a pivot table. The two
fields in the pivot table which I'm referencing are sales and Profit dollars.
I want the calculated field to show gross profit dollars (percent to total
sales represented by profit), but without any error values (!value, div/0,
etc.) where there are no sales for a particular line (i.e. customer) in the
timeframe I'm analyzing. The formulae I've tried are:

=If(iserror(Profit/Sales),"",(Profit/Sales))
=if(isna(Profit/Sales),"",(Profit/Sales))
=if(isnumber(Profit/Sales),(Profit/Sales),"")

I'm not sure why these aren't working. I keep getting error values in the
cells which should be blank. Any help would be appreciated.
 
D

Debra Dalgleish

You can't display text values in the data area, so if you change the
empty string ("") to a zero, the formula should work. You can change the
worksheet options to hide the zeroes.
 
Top