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.

Pasko1 wrote:
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 a

=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.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html