Excel Pivot Tables - Calculated Fields How to Return Blank Cells

excel excel-formula pivot pivot-table calculated-field
gcefaloni picture gcefaloni · Jan 20, 2017 · Viewed 7.3k times · Source

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

Bad_Neighbor picture Bad_Neighbor · Jan 21, 2017

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.

Related questions

Shortcut to Apply a Formula to an Entire Column in Excel

If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows! Is …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More
Simple Pivot Table to Count Unique Values

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on. For instance, I have this: ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456 What …

Read More