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)

 

[KevCarter]

Both isBLANK and isTEXT worked for me, however I tested on my Mac, and entered in the following manner rather than just using Field2.

=ISBLANK(GETPIVOTDATA("AMOUNT",$F$15,"PAYEE","Apple"))

 

[yky]

I created a calculated field in a pivot table and try the following three formulae:

=IF(Field2=0,Field1,Field2)
=IF(Field2="",Field1,Field2)
=IF(LEN(Field2)=0,Field1,Field2)

Only the first one works. The other two don't work, as if there are things in the cells. Null strings?

 

[eduzs]

Try:

=IF(Field2,Field1,Field2)

 

[mc-lemons]

In your formula are you meaning to write

isBLANK(Field2)

I'm guessing if isTEXT doesn't work, isBLANK won't either, but worth a try...

Yeah, ISBLANK doesn't work outside the PT or in the calculated field. But thanks for continuing to try.

 

[mc-lemons]

I think I've determined that formulas within a Calculated Field do not differentiate between zeros and empty/blank cells. I created a test calculated field with the simple formula below, and the it returned a zero (0) for all cells that actually had a value of zero AND those that were blank. This explains why a lot of the above suggestions didn't work. So unfortunately, I think what I am trying to do is impossible with calculated fields. Bummer.

=Field2

 

[KevCarter]

I think I've determined that formulas within a Calculated Field do not differentiate between zeros and empty/blank cells. I created a test calculated field with the simple formula below, and the it returned a zero (0) for all cells that actually had a value of zero AND those that were blank. This explains why a lot of the above suggestions didn't work. So unfortunately, I think what I am trying to do is impossible with calculated fields. Bummer.

=Field2

WOW, very strange. I keep hoping one of the Experts will chime in with an answer...

 

[mc-lemons]

WOW, very strange. I keep hoping one of the Experts will chime in with an answer...

Me, too! Any idea how we coax them to view the thread?? They usually have creative workarounds, even if it doesn't look like something is possible.

 

[KevCarter]

Me, too! Any idea how we coax them to view the thread?? They usually have creative workarounds, even if it doesn't look like something is possible.

I wish I knew!

 

[mc-lemons]

The problem may actually be that no formulas other than SUM can be used in a Calculated Field. Just basic math operators. See the "About Calculated Fields" bullet point in the link below.

Excel Pivot Table Calculated Field vs Calculated Item

Compare Excel pivot table calculated field and calculated item. How they work, when to use, limits, warnings, examples. Step by step video, Excel workbooks

www.contextures.com

 

[eduzs]

Try:

=IF(Field2,Field1,Field2)

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.
I have basic skills in pivot table, but learning more.