Pie Chart Data Range
Jan 18, 2009
I am coming across an error when i am trying to make my final chart. I am trying to chart on ten pieces of data, but for some reason when i complete the chart...it is only reporting on 7 out of the 10. if i try to do it manually, after i have selected my 7th piece of data, it will not let me select any more.
find attached a copy of my report. Hopefully it does not look too confusing. Note: Sheet "Agent Analysis" works fine...this may give you an idea of what i am trying to acomplish. It is sheet "AUX Breakdown" where i am having the issues.
View 2 Replies
ADVERTISEMENT
Mar 16, 2014
I have data in columns A to Z. Column A has the name of Bonds and columns B to Z have different returns for those funds. I would like to create a scatter plot chart that allows me to switch which column to pull the data from in addition to column A which will always have the name of the Bonds.
For example in addition to the name in column A I would like the chart to plot data from Columns C and F on the x and y axis respectively.
I'm thinking of copying column A to a new tab and then having a drop down box in columns B and C to copy over values from the 2 columns I select. How can I get a drop-down box to copy over values from another column?
View 2 Replies
View Related
Aug 31, 2006
I have created a chart that automatically expands as more data is added. This works great by using named ranges in my Chart's Series' Values and Axis Label fields. The named ranges use the Offset function.
The problem I am having is applying this same technique to to same chart to vary the number of series that I want to chart. I created a name range, using the offset function. But when I add the name to my Data Range formula, it will not stick. It changes the number of series displayed correctly when I add the name. But after I click OK on the Source Data dialog box, the data range reverts back to the address supplied by the name range variable; it does not retain the name range variable itself. Can Chart Data Ranges not contain a name range variable, especially when name range variables are used in the series formulas?
View 5 Replies
View Related
Jul 7, 2014
I am currently building a tool that works with an undefined range of input variables, e.g. I now have a range of 200 values but it could just as easily be 400 or 100. I am using these values for further calculations and have thus 'dragged down' to cells that in some instances are empty, resulting in values that are 0. I'm also building a chart based on these values, but it shows the values that are 0 as well!
Is there any way to just plot the non-zero values without changing the data range?
View 1 Replies
View Related
Feb 14, 2014
I have a spreadsheet that I have setup to have raw data pasted into. Long story short, it pulls the maximum reading for a given 5 minute period and returns the highest value for each 5 minute increment where there was a reading. It does this for all of the different dates entered, returning a separate data table for each date.
What I now need is to graph the data on a line chart. Since the number of time values can be variable, I have to include the entire range (for example A3:B5002). When I graph this out, it has all of the data clumped up at the beginning and flatlines for 99% of the chart.
I tried building dynamic named ranges using offset, but that seems to yield similar results once charted.
See the link below where I've dumped some sample data in (it wouldn't let me upload due to the size and I can't upload only part of it without breaking the whole thing). The data I need to chart is from columns A&B on the GraphData sheet. Sheet 1 contains the chart I have been trying to use. The endgame of this is to have the end user paste in their data and *POOF* charts.
My only restriction is that I cannot have Macros/VBA.
[URL]....
I tried following this post and using NA() instead of ""...no dice, it just puts #NA at the bottom labels instead of spaces.
View 3 Replies
View Related
Sep 16, 2006
I need this chart to show 3 bar colors based upon values from b5:b14 (data column). This chart is based upon the last 10 draws, so since there are 3 positions, the hit frequency expectancy would be (10 draws/3 positions = 3.33 rounded to 3). So now I would like my bar graph colors RED for any values above "3" in b5:b14 (data column), GREEN for any values equal to "3" in b5:b14 (data column) and BLUE for any values below "3" in b5:b14 (data column).
Attached is a sample chart
View 9 Replies
View Related
Nov 30, 2006
Each week I have to add a new column to a report and then update the source data of all the charts that use that newly added column. I am trying to create a macro that will use the last 12 columns of data located on a different worksheet as the range for the source data of the chart. I have attached an excel document that contains an example of how one of the charts that I am using looks and where it pulls the data from.
View 3 Replies
View Related
May 23, 2008
I have data which is imported from a txt file and always starts at the same row (R19) but is variable in length (end of data R??). I want to be able to select the chart xvalues (R19C10:R?C10) and values (R19C4:R?C4) based on the number of cells with data in them for a given column. In addition I believe I'm having troubles with sheet references, when the txt file is opened the sheet name is set to the file name, since I want to use this macro on many different files I need the sheet referencing code to account for this. Anyway here is the section of code which seems to be giving me trouble (the initial sections just create a new column of data which needs to be referenced as the xvalues) ...
View 3 Replies
View Related
Jan 21, 2014
I have created a chart but for some reason rather than the names appearing there are numbers. I did the chart successfully and then removed the data so that I could input another team's results and this happened.
View 3 Replies
View Related
Feb 1, 2010
I'm trying to do a simple loop which creates charts based on an ID number. I recorded a macro and has tried to modify it but am having trouble defining the correct reange when settign the data source. Here is my
View 2 Replies
View Related
Jan 13, 2010
Currently I'm working in an excel file that contains a lot of charts. I want to write a macro that changes the source data of every serie in every chart. It only needs to add extra rows. But as every series has different columns where they get the data from, how can I change the rows without changing the columns?
This is what I've written so far:
Code:
Sub ammend_datarange_series_all_charts()
Dim start_cell As String, end_cell As String
Dim chart_tot As Integer, c As Integer, s As Integer
[Code]....
As you can see I've tried two things, first I tried to change the string of the source data, however this meant that every series would have C and E as their source data. So I wanted to retrieve the x and y values in string format. This does not work yet, it returns an array with the values of all the entries in the series source data. From that array I can not retrieve the column where the data came from.
View 4 Replies
View Related
Oct 11, 2009
I have a worksheet with 300 rows and I have a separate worksheet that contains graphs based on the data in the 300 rows.
The data is continually updated but stays at 300 rows. I add a new row at 301 and then I delete row 1.
I set the graphs up with a Chart Data Range of =GData!$B$1:$AB$300
As I add and delete rows the Chart Data Range reduces so after adding and deleting 3 rows the Chart Data Range is now =GData!$B$1:$AB$297
How can I get the Chart Data Range to stay at =GData!$B$1:$AB$300?
View 9 Replies
View Related
Nov 30, 2006
I am attempting to use a dynamic named range in a chart data series as described in Excel Hacks (Hack #42 and Hack #52).
Using Insert|Name|Define I have created a named range called CashFlowSaleChartDataRange that is set to the following value:
=OFFSET('Cash Flow-Sale'!$O$10,0,0,NumFlows,1)
When I subsequently assign a reference to this named range in the data series dialog it will accept the answer and my chart will adjust to reflect the updated range. However, if I return to the data series dialog, I now see the range address returned by the OFFSET function rather than the named range reference.
View 2 Replies
View Related
Jun 16, 2008
I've read through every thread I can find about dynamic named ranges as source data for a chart. It seems as if everyone can get it to work but me. I have a chart on one sheet that needs to update dynamically from data on another sheet when a button is clicked. Here's my code that runs when I click the button:
Sub Update_Center_Chart()
Sheets("Center Data Chart").Select
ActiveSheet.ChartObjects("Center Data").Activate
ActiveChart.SetSourceData Source = Range("CenterData")
End Sub
I get the error message: "Type mismatch." The data is in B1:F2 on a different sheet. The formula in the named range, "CenterData" is this:=OFFSET('Center Data'!$B$1,0,0,COUNT('Center Data'!$B:$B),4). The data will expand by 1 row every time.
View 5 Replies
View Related
Mar 7, 2014
I have a set of data listed by country, I need to add a new country to the list and I want to keep everything in alphabetical order. My problem is that adding a new series to the range anywhere but the end throws of the series formatting for my chart. The plotted points on my chart are the countries flag, so when everything shifts down a row the flags no longer represent the proper country. Is there an easy way to add the new country and keep my formatting lined up or will I need to redo the formatting for all the alphabetically lower countries?
View 5 Replies
View Related
Jun 4, 2012
I have a user that uses pivot tables and charts every month to do a report. He wants to copy his charts every month and then just change the month in the data for the pivot table, but not matter what he tries its tied to the previous month and then chart data range in the select data source properties is grayed out. how to get this to work?
View 3 Replies
View Related
Jan 14, 2014
I need to plot various data on top of stock charts.
This data could be irregular in date: it could be weekly, or simply random.
To plot this data by itself requires a Scatter Chart.
From what I know so far, you CANNOT DO THIS.
However, I suspect this could be done if I build a Stock Chart from scratch using a Scatter Chart.
Error bars can be used to make the tails, however, I don't know how they built the body bar which has the characteristics of a bar (border, and interior.)
But since I don't really need those two characteristics, I just need a wider error bar line that is provided in the chart edit window. I'm guess through a macro, there are wider line widths assignable.
View 5 Replies
View Related
Nov 27, 2011
I am looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2)
these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.
View 1 Replies
View Related
Dec 29, 2011
I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
View 1 Replies
View Related
Mar 26, 2009
I am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period).
So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.
View 4 Replies
View Related
Jun 30, 2008
I have a chart with 3 data series. The series are located in columns A, B and C respectively. Series I is a general number anywhere from 0 to 100,000. Series 2 is also a number, but is is devided by series 1. So, if series 1 was 100,000 then series two would be 25,000/100,000 which is 0.25. Series three will always be numerator of series 2 or 25,000 in this example. The numerator, or the 25,000 will never change, so, in my example, series three will be a straight line across the chart because it is always 25,000.
In my example, the .25 is plotted on the left value axes, and series one is plotted on the right value acccess (secondary axis). Series three is just a line in the middle.
My problem is that sometimes the line, series 3, doesn't match the values in both the right and left values axes. So, if series one is 48,000, then series two would be 48,000/48,000 = 1, and series three would be 48000 - the straight line. The third series should be a line touching the 48,000 on the right and the number 1 on the left. It does touch the 48000 on the left, but is below the number 1 on the left.
View 9 Replies
View Related
Dec 3, 2013
I have a Choose function that is used to determine which range I need. e.g. =Choose(Choice,Cell,Cell,...).
In each corresponding "Choice" cell is a typed range. e.g. $B$33:$L$70
I'm looking for a way to be able to use this typed range as the range for some chart data.
View 2 Replies
View Related
Nov 1, 2006
I am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart:
1) Weather data (temperature) by region:
So one region, would be: Northwest
I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month.
2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region.
3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data).
What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.
View 3 Replies
View Related
Mar 24, 2009
I have daily information that I am tracking for the year. From this information I am making 12 monthly charts. When I started the file I was able to format the X axis and choose the minimum and the maximum value it would graph (1st day of month/last day of month). The graphs also show the trendline.
For some reason the February chart is set to March. When I click on the X axis and go to format axis option, I no longer have the option to set the minimum and maximum values. The first thing is lets me adjust is the interval between tick marks.
The vertical axis still lets me choose the min and max values.
View 9 Replies
View Related
Jul 23, 2014
I have created a button in an excel worksheet that when clicked, creates new tabs each containing a chart. I have tried to modify the code to position the chart within a specific range of cells on each tab, however, I have not been able to get it to work. Below is my code. Please see the code following 'Add the Chart.
VB:
Sub AddCharts()
Dim lastr As Long, r As Long
Dim shname As String
Dim ltitles As Range, rng As Range, rData As Range, x As Range, tbl As Range, r2Data As Range
On Error Goto ErrorCatch:
Set ltitles = Sheet1.Range("A3:N3")
Set rng = Sheet1.Range("A4:A" & Sheet1.Range("A3").End(xlDown).Row)
[Code] ......
ErrorCatch:
'Debug.Print Err.Number
'Debug.Print Error
If Err.Number = 1004 Then
shname = x.Value & x.Row + 1
End If
If Err.Number = 20 Then Exit Sub
Resume
End Sub
View 6 Replies
View Related
Feb 22, 2010
I am fairly new to using vba in excel, but not vba entirely so I know there is a way to accomplish what I need. I need to specify the range for a chart using variables
1. The row variable will be a selection on a combo box
2. The column variables will be mulitple selections on a list box
3. These cells will not be concurrent, for example:
I6, K6, O6
This is all in a vba macro button
View 2 Replies
View Related
Feb 27, 2012
I recorded a simple macro to create a chart using VBA. The code for the macro is:
Code:
Sub Add_Chart1()
'
' Add_Chart1 Macro
'
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
[code]....
The problem is that I need the code to select a variable range from C4 to the last used row in column C for the x values and from G4 to the last used value in column G.
I tried to modify the code to do that by doing the following:
Code:
Sub Add_Chart1()
'
' Add_Chart1 Macro
'
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
[code]....
When I run the macro, I get an error 91, "Object variable or With block variable not set"; Also the chart is created but not correctly.
I created a sub called "range" (see below) and it correctly selects selects the C or G ranges, however, the argument does work in the chart macro.
Code:
Sub Range()
Sheets("Sheet1").Range("C4:C" & Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row).Select
Sheets("Sheet1").Range("G4:G" & Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row).Select
End Sub
View 2 Replies
View Related
Apr 24, 2006
I want to have the "category (x) axis label" to change with each new entry into column A. I would like to start from the last entry and go back 30 and have that change each time there is a new entry. I guess what I am trying to say is I only want to chart the last 30 entries.
View 2 Replies
View Related
Nov 29, 2006
I am trying to link data that has been entered into a inputbox with a range of cells.
My goal is to allow the user to answer: how many people sat the exam?
Therefore when a chart is automatically produced only the cells with the required data are selected.
So, my range is A2:C*
* being the user input.
View 4 Replies
View Related
Aug 30, 2012
I am working on automating a report via vba code where the range of the chart needs to be changed every time the macro is run. Basically the chart is a snapshot of performance of 6 months .
For example : if the chart is already displaying Jan - Jun data , when the information for July is added and report updated the chart should be updated to reflect Feb - Jul data .
There are many graphs but have added 2 sample here for ref .
View 5 Replies
View Related
