bar of pie trouble

[mr tom]

Hi,

I've got a dashboard system in excel which cycles through about 400 salesmen
and produces individual dashboards for the lot.

Because of this, they all have to work the same way.

We've been looking at having a chart to pictorally represent which products
they sell.

Initially, i looked at a pie, but ran into a problem - 95% of their sales
may be investment products, with the remaining 5% split between all the other
types, e.g. pensions, annuities, protection etc. On a pure pie, this is
unworkable as it looks awful, so I switched to bar of pie.

Using bar of pie wasw great. I said that anything with less than 5% of the
total should end up in the bar, so small stuff was still visible.

Then I ran into some of our salesmen who only actually sell a couple of
products, say a 50:50 split. This splits the pie, leaving 2 leaders to an
empty bar chart and a stack of superimposed bar chart labels. It looks awful.

Can I suppress the leader lines and labels for zero entries?

I know that if it was a static chart, I could simply remove the values from
the series, or delete them altogether, but where this has to work for 400
people, this simply isn't an option.

Likewise, I've tried (against the labels) =IF(the value is zero then return
"") and the same on the value, but that makes the data table rubbish as the
table shows year on year comparison, so missing titles are not a cunning
plan...

Anybody got any ways around this?

Thanks.

 

[Andy Pope]

Hi,

If you remove the zero entries form the chart the labels and leader lines
will not appear.
You can use Autofilter to do this using a custom filter of "is greater than"
0

Cheers
Andy

 

[mr tom]

Hi Andy.

That's a really good idea.

Just one more thing - the bar of pie has two leader lines from the pie to
the bar, showing how it "explodes" out of it. If the bar chart is absent
because e.g. there are no values below 5%, then it is not shown, but the
leader lines still are.

I considered turning them white or removing them, but then it wasn't clear
that the bar chart was an expansion of the contents of the pie...

Am I trying to have my cake AND eat it?

 

[Andy Pope]

For me when the bar has no data the bar appears as a diagonally shaded box
with no lines.
Which version of xl are you using.

Cheers
Andy

 

[mr tom]

2002.

Don't worry too much - I think we're going to go back to a pure pie.
Anybody can understand those!

Cheers,

Tom.