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

mc-lemons

New Member
Joined
Apr 30, 2012
Messages
46
Hi all!

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

Excel Formula:
=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)"

Excel Formula:
=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!

Excel Formula:
=IF(Field2=0,Field1,Field2)
 

KevCarter

Board Regular
Joined
Dec 7, 2013
Messages
145
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Both isBLANK and isTEXT worked for me, however I tested on my Mac, and entered in the following manner rather than just using Field2.

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

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,835
Office Version
  1. 2010
Platform
  1. Windows
I created a calculated field in a pivot table and try the following three formulae:

VBA Code:
=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

Well-known Member
Joined
Jul 6, 2014
Messages
693
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Try:
Excel Formula:
=IF(Field2,Field1,Field2)
 
Last edited:

mc-lemons

New Member
Joined
Apr 30, 2012
Messages
46
In your formula are you meaning to write

Excel Formula:
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

New Member
Joined
Apr 30, 2012
Messages
46

ADVERTISEMENT

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.

Excel Formula:
=Field2
 

KevCarter

Board Regular
Joined
Dec 7, 2013
Messages
145
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

Excel Formula:
=Field2

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

mc-lemons

New Member
Joined
Apr 30, 2012
Messages
46

ADVERTISEMENT

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

Board Regular
Joined
Dec 7, 2013
Messages
145
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Apr 30, 2012
Messages
46
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.

 

eduzs

Well-known Member
Joined
Jul 6, 2014
Messages
693
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Try:
Excel Formula:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,122
Messages
5,628,827
Members
416,342
Latest member
BlueDevil12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top