[Gram123]

Hi there. I've been asked to produce a pie chart in Excel, which shows volumes of vehicles damaged per ship.

This info is stored in a 4 column table:
- Ship Name
- Number Imported
- Number Damaged
- Percentage Damaged (being Number Damaged divided by Number Imported, expressed as a percentage).

I might get up to say, 10 different ships in a month.

I want the pie chart to show 3 data labels. If I go to Format Data Series > Data Labels tab, under the "Label Contains" section I can use:
Category Name to give me the Ship Name.
Value to give me the Number Damaged.

But I also need to show Percentage Damaged.

However, I cannot use Percentage under "Label Contains", because, of course, this shows the percentage of the pie that each slice represents, rather than the percentage Values that are calculated.

I tried messing around with a concatenated field and custom cell formatting, but I couldn't get it to show what I wanted. Ideally I'd like the number imported included on the graph too!
So I might ultimately have a data label showing:

Autofreighter
160 damaged / 1000 imported = 16%
or something similar. Perhaps without the words "damaged" and "imported"...

I realise these percentages may appear misleading on a pie chart where one might reasonably expect the whole pie to add it up 100%, but these are my instructions!

Any pointers?

 

[Anne Troy]

Gram: Pie charts only show one SERIES of data, and aren't designed for comparing two different types. In other words, it's great for:

Expenses:
--Rent
--Electric
--Water

But not for comparing income and expenses. You're trying to use a pie chart to display data it was not designed to display.

I'd be trying a different type of chart.

 

[XL Guru]

>> Any pointers?

AVOID CHARTS ; CHARTS ARE EVIL.

>> If I go to Format Data Series > Data Labels tab,
>> under the "Label Contains" section

I don't have a "Label Contains" section - what version is this?

See attached example ; Show Formulas is turned on (using CTRL and the key below Esc - this is a toggle setting) so you can see the formula structure.

I created a straight pie from A1:A3 and C1:C3, Legend on, Data Labels = show value.

Then I selected D2 & D3, dragged it & dropped it onto the Legend.

Bizarre eh?

HTH (but unlikely),
Andy

 

[Gram123]

Dreamboat: Thanks for the advice. I actually only want to show one data series on the pie - and I've just realised that I don't know if they want that series to be the Number Damaged or the % Damaged...
Anyway, I was hoping to have the other info just appear on the data labels, not be actual comparable series'.

Andy:

>> Any pointers?

AVOID CHARTS ; CHARTS ARE EVIL.

Ha. Well, seeing as I'm doing a brochure of about 80 different charts (this being one of the last couple to fix) that's no good to me!

Version is Excel 2002, BTW.

Okay, so I modified what you'd pasted onto your legend to this:

=A130&" - "&C130&" of "&B130&" vehicles damaged "&"("&C130/B130%&"%)"

Which gave me this:
Autofreighter - 13 of 558 vehicles damaged (2.32974910394265%)

This evidently just alters the series name, so I wouldn't need to show the Value on the data label, just the Series Name.

Can I truncate the percentage to 2dp within that formula?

Also this would mean that the legend shows the same info that the data labels do, so I'd only have to have one or the other on my chart...

Gram

 

[Gram123]

Okay, 3 things...

1) It is the % Damaged that is the series that I need to display.

2) I figured out how to show the % Damaged to 2dp:
=A130&" - "&C130&" of "&B130&" vehicles damaged "&"("&ROUND(C130/B130%,2)&"%)"

3) I've been given the go ahead to change this from a pie chart to "something else"... Most likely a bar chart with a data table.

Apparently the existing chart has been showing the wrong percentages (i.e. someone's displayed the Percentage under Data Labels) for 5 years and nobody noticed! Oh dear...

So now we've practically solved it, I can go ahead and do it in a more appropriate chart type! Typical...
Anyway, see the pretty-much-corrected version in the attachment if you're interested.

Thanks you two.

Gram

 

[XL Guru]

>> see the pretty-much-corrected version

Surely "pretty, much-corrected"?

The bottom line is congrats, you seem to have pretty much nailed this now (who'da thunk dropping data onto Legend would do anything?).

Tho' if you do opt for "something else", stacked column would probly be favorite.

Best rgds,
Andy

 

[Gram123]

I saw another thread about dynamically naming source data so that charts 'update themselves'.
I was wondering, as I have 80 odd charts here, can I do something similar to update them?

On some graphs the source range would not change 'size', it would just shift. Most of my charts are measuring processes over time, either by Month or Week Number.

The source data for Monthly charts will cover a set number of intervals (usually 12 months), but these will shift down (or along) the source table, with the column title (axis title) always remaining in the same place.
For example, say I have a table of Total Valets per Month, covering Jan 2003 to Feb 2004.
In the first month of producing this chart, I'd want it to display data for Mar 03 to Feb 04.
Next month I'd want it to display Apr 03 to Mar 04.
Etc etc.

With the Week Number charts, sometimes we have a 4 week month and sometimes we have a 5 week month, so these would also have to be able to expand.

Gram

 

[XL Guru]

>> can I do something similar to update them?

Yes.

Brand new sheet. A1 = "Month".

A2:A13 = "Jan 03", "Feb 03", ... "Dec 03". (NB - preformatted as text).

In D1 goes your start month, e.g. "Apr 03" (text also). In E1,

=MATCH(D1,A:A,0)-1

Insert -- Name -- Define. Name = "Rolling6" (no quotes), Refers to =

=OFFSET(Sheet1!$A$1,Sheet1!$E$1,0,6,2)

Click OK.

Press F5, type "Rolling6" (no quotes), press Enter. A5:B10 should get selected.

Change D1 to "Jun 03", do the F5 thing again. A7:B12 should get selected.

Geddit?

HTH,
Andy (up the Boro'!)

 

[Gram123]

Yep, I get it so far...
How do I apply this to the chart's data series?

I tried following the latter part of the example at this link, but I kept getting "invalid external reference" errors...

Gram (up the Toon!!)

 

[XL Guru]

>> Gram (up the Toon!!)

Oops, sorry. One should never make assumptions.

Rgds,
Andy

 

[XL Guru]

OK, I have labels in A1 & B1, "Month" & "# Damaged".

In A2 downwards I have "Jan 03", "Feb 03", etc. In B2 downwards, arbitrary numbers.

The defined name "Last12" with a formula of

=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-12,0,12,1)

should equate to the last 12 filled cells in B:B (I possibly misled you by giving an example where the range had 2 columns).

The defined name "Labels" with a formula of

=OFFSET(Last12,0,-1)

should give you the last 12 filled in A:A (as per the link).

If I build e.g. a column chart with a data range of

=Last12

, all's well except XL initially translates this as

=SERIES(,,Sheet1!$B$11:$B$22,1)

If I manually change this to

=SERIES(,Sheet1!Labels,Sheet1!Last12,1)

, XL then translates as

=SERIES(,Book1!Labels,Book1!Last12,1)

If I then add data below what I had in the first place (A2:B22), it auto-updates the series.

HTH,
Andy

 

[Gram123]

Okay, as long as I have the months formatted as text, it works.
However, it brings me to a couple of additional questions:

1) Although most of my monthly graphs have data stretching back at least a year, a couple don't.
I tested your method with values only in the last 11 of the cells. When I do this, "Month" appears under the first data point on the x-axis. In other words, it's picking up the column title as though it were a value.

When I only have data in the last 10 cells I get the "invalid external reference" error message, as obviously there's nothing before row 1.

I wouldn't receive this error msg for my charts, because as I mentioned earlier, all data begins on the second page - roughly in row 60. However, I would get Month appearing on the chart.

So, I realise this may be extremely awkward or even impossible, but is there a way to do exactly what you've noted here, but allowing for less data points until we get to 12? So If I started a new chart in Jan 2004 it would currently only display 3 data points for Jan, Feb and March, but would continue to expand until December, and thereafter it would move to pick up the last 12 months....

2) And just to make it more complicated... I'm producing these charts once a month. For the purposes of displaying charts that have weekly data, I have to use "working weeks"

So whereas all monthly charts display a nice standardised 12 month period, weekly charts show 3 months with a slightly varying number of data points.

E.g. There were 5 "working weeks" in January 2004:
Week 1 - 29/12/03 to 04/01/04
Week 2 - 05/01/04 to 11/01/04
Week 3 - 12/01/04 to 18/01/04
Week 4 - 19/01/04 to 25/01/04
Week 5 - 26/01/04 to 01/02/04

So, if I was displaying Aug 03 - Oct 03, there'd be 14 data points. If I was displaying Jan 04 - Mar 04, there'd be 13 data points.
If I was displaying Feb 04 - Apr 04, there'd be 12 data points.

Ha ha. So, I realise there's probably no simple solution to this, other than going in and manually changing the source data range every month for every weekly chart.

Phew.
Gram

 

[XL Guru]

Hi Gram. I don't have much time today, I'll just focus on the para

>> So, I realise this may be extremely awkward
>> or even impossible ...

For the purpose of this, B1 holds the label "Month".

I think what you want is ; if B2 holds data, the range = B2 ; if B2:B3 hold data, the range = B2:B3 ; if B2:B13 hold data, the range = B2:B13. But if B2:B14 hold data, the range = B3:B14.

Try

=IF(COUNTA(Sheet1!$B:$B)<13,OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1),OFFSET(Sheet1!$B$2,COUNTA(Sheet1!$B:$B)-13,0,12,1))

for your named range (formula).

I'll try & get back to you re: weekly charts when I've more time.

Rgds,
Andy

 

[Gram123]

I've considered the "week" problem, and have decided to stick with the easier method of using a set number of data points regardless of the number of weeks per month. As long as the last (i.e. third) month's data is displayed, I don't suppose it matters too much if a week is missing from the beginning of the period. Plus I can argue that a standardised number of plot points on these charts looks better when comparing data from one month to the next.

So, I'll give your suggestion a go for the monthly charts and scrap the "working weeks" problem. I can live with it using the same 12 interval method.


Gram

 

[Gram123]

Andy,
Your solution to the monthly chart works perfectly!
That's exactly what I was after. This will save much, much effort and is greatly appreciated. As you can imagine, changing the source data for 80 odd charts can be a bit of a pain, so this is an enormous help.

Cheers mate!

Gram

 

[XL Guru]

No worries. My bad on the error for not testing enough.

Finally, you may be able to save some naming set-up work with a global name for sheets where the data layout is identical (i.e. label in B1, data below). By just stripping out the sheet refs in the formula, e.g.

=IF(COUNTA(!$B:$B)<13,OFFSET(!$B$2,0,0,COUNTA(!$B:$B)-1,1),OFFSET(!$B$2,COUNTA(!$B:$B)-13,0,12,1))

You test this by pressing F5 on the relevant sheet(s), typing the name, pressing Enter.

Rgds,
Andy

 

[Gram123]

If I wanted to add another series to the chart is it just a case of changing the range in the "Last12" Defined Name?
I have some charts that would be like your example with another series (or two), say "Other Damage" in the C column. I alos have some charts where the data for this second series may be in, e.g. Column F.

Gram

 

[XL Guru]

Hi Gram, I'm offline for a while. If no reply in a day or two, bump this.

Rgds,
Andy

 

[XL Guru]

Hi Gram.

>> If I wanted to add another series ... is it just a case
>> of changing the range in the "Last12" Defined Name?

Dunno m8, I never have cause to work w/charts. Try it. Going back to the sheet-specific formula-named range

=IF(COUNTA(Sheet1!$B:$B)<13,OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,2),OFFSET(Sheet1!$B$2,COUNTA(Sheet1!$B:$B)-13,0,12,2))

, the emboldened 2s are where you'd increase the # of cols theoretically. Whether the chart would accept it or not is easier for you to test.

Going back to the "working weeks" issue, can't helping feeling that you should throw in an extra column with formulas to return the month (?#?), then have a bash at a pivot chart/table.

Rgds,
Andy

 

[Gram123]

Hi Andy,
I tried what you suggested, but just ended up with bizarre results and a confused mind.
However, I found this site, which gave me what I was after.

Now all I have to do is update all of those damn charts!

Many thanks for your help.

Gram