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.

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