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

mc-lemons

New Member
Joined
Apr 30, 2012
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
also you cantry with
ptblank.png

or unmark and get 0
 
Upvote 0
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?
 
Upvote 0
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)"
 
Upvote 0
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...
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

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