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

#### mc-lemons

##### New Member
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.
It actually was very close to working, except that I had to swap the if TRUE/FALSE order (see below). And also, when a zero is in Field2 (versus a blank), I want it to return Field2, but the formula returns Field1. I was just messing with some basic logical tests with IF and OR formulas, and the results did not make sense, so I feel like those formulas inside the calculated fields are not being processed as they normally would. Hence, my latest comment.

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

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### eduzs

##### Well-known Member
Like that?
Excel Formula:
``=IF(OR( F2=0, F2), F2, F1)``

#### eduzs

##### Well-known Member
Sorry, really does not works. Hope someone else can help.

#### eduzs

##### Well-known Member
Based on this thread (that I think is true), pivot table cannot differentiate between zero and blank fields, and you should use a workaround:

Replies
22
Views
1K
Replies
1
Views
267
Replies
1
Views
521
Replies
4
Views
139
Replies
8
Views
241

1,127,623
Messages
5,625,935
Members
416,143
Latest member
JoyceMB

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