Calculated Fields showing error results
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 |
All times are GMT +1. The time now is 08:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com