PT Calculated Fields: Differentiate Between Zero and "(blank)"

[mc-lemons]

Hi all!

I'm trying to created a Calculated Field in a Pivot Table with an IF statement that goes something like this

=IF(Field2="(blank)",Field1,Field2)

But Excel doesn't seem to find any "(blank)" cells when that formula is entered into the calculated field, even though the below formula returns TRUE when placed in a cell outside the Pivot Table, as a check (Note the value in G3 is "(blank)"

=G3="(blank)"

So it seems that a regular formula vs one used for a Calculated Field handles the blank cells differently.

If I change the "(blank)" to a 0 as shown below, it works, but then any cell that actually has a zero in it (vs "(blank)") is considered TRUE in the logical test. I need the calculated field to only consider actual blank cells as TRUE and everything else as FALSE (including zeros). But I'm not sure how to write the IF statement to differentiate between the two. I appreciate any help on the matter!

=IF(Field2=0,Field1,Field2)

 

[mc-lemons]

I tested this on a very simple pivot table and it worked on a very simple hypothetical test. But I don't know if it is the right or the best way to do it or if this works in all kind of fields/pivot tables.

It actually was very close to working, except that I had to swap the if TRUE/FALSE order (see below). And also, when a zero is in Field2 (versus a blank), I want it to return Field2, but the formula returns Field1. I was just messing with some basic logical tests with IF and OR formulas, and the results did not make sense, so I feel like those formulas inside the calculated fields are not being processed as they normally would. Hence, my latest comment.

=IF(Field2,Field2,Field1)

 

[eduzs]

Like that?

=IF(OR( F2=0, F2), F2, F1)

 

[eduzs]

Sorry, really does not works. Hope someone else can help.

 

[eduzs]

Based on this thread (that I think is true), pivot table cannot differentiate between zero and blank fields, and you should use a workaround:

Pivot Tables - Calculated Field - How to return empty cells

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...

www.mrexcel.com