# 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)

#### KevCarter

##### Board Regular
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"))

### 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
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
Try:
Excel Formula:
=IF(Field2,Field1,Field2)

Last edited:

#### mc-lemons

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

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

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

Replies
22
Views
1K
Replies
1
Views
270
Replies
1
Views
525
Replies
4
Views
139
Replies
8
Views
244

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.

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