Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Line Chart - show percent and value?
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! |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Line Chart - show percent and value?
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 http://PeltierTech.com/ _______ PK wrote: 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! |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Line Chart - show percent and value?
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" wrote: 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 http://PeltierTech.com/ _______ PK wrote: 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! |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Line Chart - show percent and value?
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/ _______ PK wrote: 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" wrote: 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 http://PeltierTech.com/ _______ PK wrote: 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! |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Line Chart - show percent and value?
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/ 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" wrote: 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 http://PeltierTech.com/ _______ PK wrote: 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! |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Line Chart - show percent and value?
Ed - that is EXACTLY what I was looking for!!!! THANK YOU!!!!
Jon - Thanks for your help also! "Ed Ferrero" wrote: 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/ 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" wrote: 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 http://PeltierTech.com/ _______ PK wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|