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

#### mc-lemons

##### New Member
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)``

### Excel Facts

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

#### sandy666

##### Banned - Rules violations
maybe try "" instead of (blank)

#### mc-lemons

##### New Member
maybe try "" instead of (blank)
I did try that, but it must have not have returned a TRUE for the blank cells, since all cells returned the value for the FALSE outcome

#### yky

##### Well-known Member
Maybe LEN(Field2)=0

#### sandy666

##### Banned - Rules violations

also you cantry with

or unmark and get 0

#### mc-lemons

##### New Member
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

Excel Formula:
``=isTEXT(Field2)``

#### mc-lemons

##### New Member

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

Replies
22
Views
1K
Replies
1
Views
250
Replies
1
Views
519
Replies
4
Views
137
Replies
8
Views
231

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.

### Which adblocker are you using?

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

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