Pivot Tables - Calculated Field - How to return empty cells

[gcefaloni]

Hi guys,

I'm working with pivot tables in excel and historical monthly returns for different mutual funds. I'm trying to calculate the historical monthly returns of each fund net of all management fees. So I have a series called 'MonthlyReturn' and one with the annual cost of each fund called 'MER'. Basically, what I'm having a hard time doing is to return empty cells within the pivot table for months where there are no data rather than just showing (0-'MER'/12) which wouldn't be representative of the real returns. The fund was not negative 'MER'/12, it just didn't exist back then.
The formula I've been using in my calculated field of the pivot table is the following: =IF(MonthlyReturn="","",(MonthlyReturn-(MER/100/12))). The problem is that this is returning 0s rather than showing blank cells for the months that don't have monthly returns data in them.
How would I go about making sure that if MonthlyReturn for a given fund in a given month has no data attached to it, that it would return an empty cell rather than attempting to calculate MonthlyReturn - Cost/12?
Thank you.
Any help is going to be extremely appreciated.
Gabriel

 

[Jerry Sullivan]

Hi Gabriel,

The values area of the PivotTable can only return numeric values or errors. There are a few workarounds you can use to have numeric values or errors displayed as blanks. Those include:

1. Changing the worksheet setting to show all cells with zero values as blanks.
2. Change your formula to return an NA() and changing the PivotTable settings to have all errors displayed as blanks.
3. Use conditional formatting to have cells in the PivotTable that have a specified value display as blanks using a custom NumberFormat.

An essential consideration in choosing the best option is whether having the calculated field return a value of 0 or error will not adversely affect the sub-total or total values in your PivotTable.