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

Greatgranduncle

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.

Matronna

and what if I want my table to look nice and rather have blank spaces instead of ugly #N/A ? – And

You'll need to click on the checkbox "For error values show" on the "Layout & Format" tab in the Pivot Table Options. The grey box to the right of the checkbox will turn white when checked. – Matronna

Unfortunately this didn't work for me, as the PivotTable field is blank but is still treated as 0 in the formula. So my PivotTable field value is "", but the calculated field referencing it evaluates as true for =0 and false for ="" or ISBLANK. – Peaslee

I ended up modifying my source values to add 0.0001 when they are zero and not blank, so now I can use =0 and the formula to keep them blank and the 0.0001 fields don't affect the other averages any more than 0. – Peaslee