Pie Charting with Missing and Changing Data

Hi All

I am trying to create a dynamic pie chart (has to be a pie chart) that
can automatically take into account the fact that the number of series
to be graphed each time changes, and that there may be missing data
within the list of data

The 'Plot Empty Cell' section under Tools - Options does not seem to
apply to Pie Charts as I cannot select any of the options here

I have tried using the formula
=OFFSET('Sheet1'!$B$7,1,0,COUNTA('Sheet1'!$B:$B)-1,1) in the named
range for the data called Spend, and =OFFSET(Spend,0,-1) in the
corresponding data labels, but I this still graphs areas where there
are missing cells

Does anyone have any suggestions how to achieve this

Thanks

Karen


--
ksp
------------------------------------------------------------------------
ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267
View this thread: http://www.excelforum.com/showthread...hreadid=546340

Hi,

Here is an example that does not plot missing data.
http://www.andypope.info/charts/piezeros.htm

Cheers
Andy

ksp wrote:
Hi All

I am trying to create a dynamic pie chart (has to be a pie chart) that
can automatically take into account the fact that the number of series
to be graphed each time changes, and that there may be missing data
within the list of data

The 'Plot Empty Cell' section under Tools - Options does not seem to
apply to Pie Charts as I cannot select any of the options here

I have tried using the formula
=OFFSET('Sheet1'!$B$7,1,0,COUNTA('Sheet1'!$B:$B)-1,1) in the named
range for the data called Spend, and =OFFSET(Spend,0,-1) in the
corresponding data labels, but I this still graphs areas where there
are missing cells

Does anyone have any suggestions how to achieve this

Thanks

Karen



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy

Thanks for that - It seems to work however I have to manually delete
any data labels for that line that has missing data

Do you know of a way to get the data labels to also update with the
changing data? The link that you gave me says that it updates the
legand but not for me.

Thanks

Karen


--
ksp
------------------------------------------------------------------------
ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267
View this thread: http://www.excelforum.com/showthread...hreadid=546340

I just downloaded and test the workbook and it functions for me.
Zero items are removed from pie, data labels and legend.

To get rid of zero data labels use a custom number format (whole values
only)
0;;;

Cheers
Andy

ksp wrote:
Hi Andy

Thanks for that - It seems to work however I have to manually delete
any data labels for that line that has missing data

Do you know of a way to get the data labels to also update with the
changing data? The link that you gave me says that it updates the
legand but not for me.

Thanks

Karen



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

I have to confess I was guilty of not reading the instructions properly
- something I give my users a hard time for

Thanks for your help this is going to save one of my users days worth
of work

Ta

KP


--
ksp
------------------------------------------------------------------------
ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267
View this thread: http://www.excelforum.com/showthread...hreadid=546340

No problem glad it helped and thanks for your honesty

Cheers
Andy
ksp wrote:
I have to confess I was guilty of not reading the instructions properly
- something I give my users a hard time for

Thanks for your help this is going to save one of my users days worth
of work

Ta

KP



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info