Line Chart - show percent and value?

[Guest]

I thought this would be easy, but I am really stumped!

I need to show the percentage in addition to the value in the data labels
for a line chart.

Example,
January February
Series 1 5 6
Series 2 15 4

I would like the January data label for Series 1 to show 5 (25%)
I would like the January data label for Series 2 to show 15 (75%)

I would like the February data label for Series 1 to show 6 (60%)
I would like the February data label for Series 2 to show 4 (40%)

TIA FOR ANY HELP!

 

[Jon Peltier]

You need to construct your labels in another worksheet range. For the
January Series 1 label it would look like

=B2&" ("&TEXT(B2/SUM(B2:B3),"0%))&")"

Then use one of these free Excel add-ins to apply the labels from the
worksheet range to the data series in the chart:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com/ss

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
774-275-0064
208-485-0691 fax
(e-mail address removed)
http://PeltierTech.com/
_______

 

[Guest]

Thanks Jon!

I am sure that would work, however this is my problem:

I am actually automating Excel from a MS Access database, and this
application will be distributed to many workstations which would not have the
add-ons.

Is there any way to do this without an add-on?

 

[Jon Peltier]

Sure, the code that the add-in uses to label the points can be run from
the automation routine in Access.

(Also, once the labels are created and linked to the cells, the workbook
does not need the add-in to display correctly.)

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

 

[Ed Ferrero]

Hi PK,

Sample Excel code to label chart points is:

Dim pts As Points
Dim LabelRange As Range
Dim stRow As Integer
Dim stCol As Integer
Dim i As Integer

' This example assumes that data and data labels are in columns

Set LabelRange = Range("B1:B6")

stRow = LabelRange.Row
stCol = LabelRange.Column

' you could prompt for chart name here
Set pts = ActiveSheet.ChartObjects("Chart 1").Chart. _
SeriesCollection(1).Points

' now show the data label for each point and set the text
' to that in the selected range
For i = 1 To pts.Count
pts(i).ApplyDataLabels Type:=xlShowValue
pts(i).DataLabel.Text = "=Sheet1!R" & i + stRow - 1 & "C" & stCol
Next i

You should be able to automate from Access using something similar.

Ed Ferrero
http://edferrero.m6.net/

 

[Guest]

Ed - that is EXACTLY what I was looking for!!!! THANK YOU!!!!

Jon - Thanks for your help also!