Excel Pivot Tables - Calculated Fields How to Return Blank Cells

Question

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

Answer 1

Two steps: first, set all of the cells you want to show as blank to return errors. i.e. change your formula to this =IF(MonthlyReturn="",#N/A,(MonthlyReturn-(MER/100/12))) or put 1/0 to return a divide by 0 error (I suggest the former as it is less computing for Excel. second, in the pivot table options, select the checkbox next to "For error values show" and leave the text box to the right blank. your problem cells disappear.