Discounting null entries from Pie & Bar Chart

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

Hello all,

I've got a simple and colourful Worksheet that updates itself live
depending on data that is pasted into sheet 2 of it.

The thing is, when people are all in it's fine, everything is plotted
correctly(http://hayn.gotadsl.co.uk/excelfull.jpg), but when several
people are absent it munches them all together. Notice the circled
segment on this screenshot below:

http://hayn.gotadsl.co.uk/excel.jpg

How can I stop that? I just want it not to plot the absent people.

--Nick.
 
Nick -

You could filter the workbook range (Data menu > Autofilter), such that
rows containing zero in the data column are hidden.

You could also put the labels in column A, the data values in column C,
and enter a formula in column B that shows the label only if the value
is greater than 0. In cell B2, this id the formula:

=IF(C2>0,A2,"")

Create your chart with columns B and C.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Nick -

You could filter the workbook range (Data menu > Autofilter), such that
rows containing zero in the data column are hidden.

You could also put the labels in column A, the data values in column C,
and enter a formula in column B that shows the label only if the value
is greater than 0. In cell B2, this id the formula:

=IF(C2>0,A2,"")

Create your chart with columns B and C.
Click to expand...

Thanks! I've re-designed the front-sheet to take advantage of your
advice, however, I can't get auto-filter to auto-filter the data more
than once.

It'll do it once when I select it and tell it to filter the empty
boxes, but when I refresh data on the 2nd sheet by inputting more
dummy data to make some zero and other higher, it doesn't
re-auto-filter it to show/not show the new items.

Perhaps I'm missing something.

--Nick.
 
You have to clear the filter and reset it.
Click to expand...

Thanks John.

I think I've created a kind-of workaround that I'm happy with, based
on your original point:

Column A: =IF(C4>0,B4,"")
*HIDDEN COLUMN*
[Blank if C blank, filled if not]
(Names, just for Pie Chart lables)

Column B: Names of all staff in that team
(Used as lables for Bar Graph)

Column C: =(Data!O5)
*HIDDEN COLUMN*
Calls taken for each team member
(This is raw, for the charts)

Column D: =IF(Data!O5>0,Data!O5,"Not In")
Says number of calls, or "Not In" if none.

As A and C are just for charts and are both hidden it all appears to
work perfectly, the only thing that is left with the pie chart is an
issue where a "blank slice of pie" showing as 0% sits in between each
person that has a valid slice of pie. The blank slice of pie doesn't
have a lable, and just sits as a 0% slice would normally sit.

It has no lable so it doesn't really bother me - that was what
*really* annoyed me! Not sure how to discount the empty slice of pie
though.

Thanks for all of your help!

--Nick.
 
Back
Top