A question about how to deal with missing data in datasource

slhappyls

New Member
Joined
Sep 17, 2006
Messages
43
I have created a pivot table computing the average of age. When I use old data source (no missing), it works, but when I use a new data source including missing values, it gives error like #DIV/0. Does anyone know how to let pivot forget missing data, only calculate on the existing ones.

Thanks
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
If you go to Table Options, you have an option "For error values show". Here you can type in what ou want instead (eg a zero).

Richard
 
Upvote 0
Thanks, but I dont want to hide the #DIV/0! info. I wanna let pivot do calculations as usual just forget the missing records.
 
Upvote 0
You could always use a helper column in the Page field which you would add to your data source. If you are having #DIV/0 errors then the helper formula would need to be along the lines of:

=C2=0

copied down (amend the range as required) to return True if the pivot formula will result in #DIV/0 and False if not. Then in your pivot table field area, you can select to only show those values with False (ie those without #DIV/0).

Make sense?

Richard
 
Upvote 0
sorry, It is not my aim.

I think #DIV/0 comes from the missing records. So that is why I ask how to let pivot table calculate by forgetting those records. Is there a way to do this.
 
Upvote 0
When you say records are missing are you referring to lines of data with blank age fields?

I really don't think that this will effect your pivot table average calculation if so.

Maybe you could post an example of your data that the pivot table is reading. It might help.
 
Upvote 0
yes, you are right! Some thing wrong. That reason why the pivot table gives #Div/0! is another reason. I got new data source, now it works well. Sorry to confused you, Thank you all.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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