Pivot Table Calculated Field - Null Values Treated as Zero

[hotbread]

I have an Excel pivot table using an Access table as the external data source. In the Access table a particular numeric field (Field1) has many null values.

In the pivot table, I have added a calculated field called "Upper" with the formula being Field1 + Field2. If the Field1 value is null, I want the Upper value to be null, but at the moment the pivot table seems to treat the null values as zero, and a value is displayed. For example, if Field1 is null, and Field2 is 320, I want the calculated field to return null, but it is returning 320.

I've tried changing the calculated field formula to: IF(Field1="","",Field1+Field2)

But the pivot table still sees the null value as zero (not "") and returns a value.

Is there any way to make the pivot table return null (or an error) in the 'Upper' calculated field if Field1 in the Access table is null?

And does anyone know why the pivot table treats Field1's null values as zero when used in the formula of a calculated field, but as null when actually brought into the pivot table as a data field?

 

[SkipVought]

Is there any way to make the pivot table return null (or an error)

Try this
[tt]
IF(Field1="",NA(),Field1+Field2)
[/tt]
In general, I try to avoid null values in numeric fields, as they cause havoc with grouping and some arithmetic. So does #N/A.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hotbread]

Thanks Skip... however, it's the first element in that formula that doesn't seem to work rather than the second - that is, Field1="" returns False even when the Field1 value is null. When the Field1 value is null, the calculated field formula always seems to recognise it as zero rather than null. Do you know of any way to make the calculated field formula recognise null values as null instead of zero, or in other words differentiate between null and zero?

In any case, I ended up putting a 'flag' field in the Access table called 'Field1 Null?' - if Field1 is null, the 'Field1 Null?' value is 1, otherwise it is -1.

Then, in the pivot table calculated field, the formula was changed to: IF('Field1 Null?' = -1,Field1 + Field2,"")

This seems to be working - the empty string returns an error in the pivot table, which is fine (I simply hide the error values so they appear as null).

Also, someone in a different forum suggested that instead of using a calculated field to achieve this, I could use the SQL property of the PivotCache object (in VBA). For example: "SELECT Field1, Field2, IIF(Field1 Is Null, Null, Field1 + Field2) AS Upper FROM ....."

I haven't tried that method yet, because there are issues with the Access database being password protected, but it may be more suitable than my solution because it would make the 'flag' fields unnecessary.

 

[TonyJollans]

I don't think you can do this in an Excel formula (there is no IsNull function and null does not equal null), so you will need to resolve the null in Access somehow - either as you have done, or in the SQL as suggested.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website