Calculated field returning #DIV/0 error but there aren't any 0's or nulls

[agray310]

I have a calculated field in a pivot table that is returning the #DIV/0 error. There are no 0's in the field and it's a numeric field so why would I be getting this erro?. I'm simply dividing delivery cost by the number of therms.

 

[RoryA]

Re: Calcualted field returning #DIV/0 error but there aren't any 0's or nulls

Is number of therms a sum or count of a source data field? If it's a count, that won't work in a calculated field unless you have Power Pivot.

 

[agray310]

Re: Calcualted field returning #DIV/0 error but there aren't any 0's or nulls

No, it's just a field with numeric data. Is there a way to post the file?

 

[RoryA]

Re: Calcualted field returning #DIV/0 error but there aren't any 0's or nulls

You can put it on a sharing site like Dropbox or Onedrive and post a link here?

 

[agray310]

Re: Calcualted field returning #DIV/0 error but there aren't any 0's or nulls

See if this works: https://1drv.ms/x/s!Ah9Xe6CeAcuFhEbI6TdWayH1Cj9a

 

[RoryA]

Re: Calcualted field returning #DIV/0 error but there aren't any 0's or nulls

The issue is that you have used entire columns for the source data. If you adjust the source range to only use the actual data rows, your field will work.

 

[agray310]

Re: Calcualted field returning #DIV/0 error but there aren't any 0's or nulls

But then how will the pivot table recognize new data added to the dataset?

 

[RoryA]

Re: Calcualted field returning #DIV/0 error but there aren't any 0's or nulls

Either use a Table or a dynamic named range as the data source.