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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

slhappyls

New Member
Joined
Sep 17, 2006
Messages
43
Thanks, but I dont want to hide the #DIV/0! info. I wanna let pivot do calculations as usual just forget the missing records.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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
 

slhappyls

New Member
Joined
Sep 17, 2006
Messages
43
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.
 

slhappyls

New Member
Joined
Sep 17, 2006
Messages
43

ADVERTISEMENT

anyone knows?
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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.
 

slhappyls

New Member
Joined
Sep 17, 2006
Messages
43
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.
 

Forum statistics

Threads
1,136,995
Messages
5,679,037
Members
419,801
Latest member
spinjector

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
Top