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)
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,833
Office Version
  1. 2010
Platform
  1. Windows
Maybe LEN(Field2)=0
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

also you cantry with
ptblank.png

or unmark and get 0
 

mc-lemons

New Member
Joined
Apr 30, 2012
Messages
46
Maybe LEN(Field2)=0
Great suggestion! I thought for sure that would work, but in all cases, LEN(Field2)=0 returned FALSE, even with "(blank)" or 0 cells. There must be a way to check for a blank cell in a calculated field. no?
 

KevCarter

Board Regular
Joined
Dec 7, 2013
Messages
145
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Please try:

Excel Formula:
=isTEXT(Field2)
 

mc-lemons

New Member
Joined
Apr 30, 2012
Messages
46
Please try:

Excel Formula:
=isTEXT(Field2)
It returned the correct TRUE and FALSE results when I did it in a column outside of the PT, just like when I tried the below. However, I did not get the correct results once that formula was plugged into the Calculated Field.

Excel Formula:
Field2="(blank)"
 

KevCarter

Board Regular
Joined
Dec 7, 2013
Messages
145
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
It returned the correct TRUE and FALSE results when I did it in a column outside of the PT, just like when I tried the below. However, I did not get the correct results once that formula was plugged into the Calculated Field.

Excel Formula:
Field2="(blank)"
Interesting, I don't have any experience with Pivot Tables but hoped it would work the same way as cells.. Sorry...
 

KevCarter

Board Regular
Joined
Dec 7, 2013
Messages
145
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
It returned the correct TRUE and FALSE results when I did it in a column outside of the PT, just like when I tried the below. However, I did not get the correct results once that formula was plugged into the Calculated Field.

Excel Formula:
Field2="(blank)"
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,127,263
Messages
5,623,700
Members
415,983
Latest member
MusicMan

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