VBA Select Data For Graphing?
Jan 28, 2013
I have a spread sheet that we input data on and produce a simple graph. When I started we plotted these graphs by hand on chart paper. When we started using spread sheets I learned how to graph the problem is these guys we have working in the field don't know how or care to learn how to make a simple xy graph.
Six months ago I started automating a workbook so our field guys could just plug in numbers and have the needed graph, I put check boxes to select the points to calculate the slope and intercept. I hard coded to plot to say line 21 then at the end of the test we always have a zero point that we don't plot so I put that on line 22 then have a macro to hide everything before I print it.
I think I would like to dynamically select the data for graphing but I'm not sure about the best way to do this. My first thought is to key on the x axis column with a loop and graph data from zero until the x looses value.
View 9 Replies
ADVERTISEMENT
Jul 2, 2008
I have researched and read multiple threads related to graphing rolling averages per month. I've setup my own but I cannot get it to graph correctly. The x axis will be the days of the month ie. 1-31. The y axis is obviously the sales volume. The legend should be a line graph for each individual month sales. I want the graph to reflect the current month and previous 12 full months.
I used the file user rockycj started for another user in a different thread labeled, "Excel Line Chart - 12 month chart moving with dates ". It was very helpful and I'm trying to get this completed before my deadline. If anybody could take a look and let me know, I would greatly appreciate the help. I have attached the file for reference.
View 9 Replies
View Related
Nov 20, 2008
I have got a table of results with wards along the columns and in the rows reading left to right weekly dates e.g. 5/10/08 12/10/08 19/10/08 etc.
How can I get a graph to change the data it displays from a combo box eg. A1 is selected in the combo box and it shows a line graph of the Data for A1 over the weeks? There are 50 wards
View 9 Replies
View Related
Dec 5, 2011
I am trying to graph data from an Array of sheets. I keep getting many errors... I tried using Cases too but this failed, it graphed the first Sheet but nothing else.....
Before Array Works! (Uses Cases)
Code:
Sub GraphTest4()
Dim LastRowGraph1 As Long
Dim ws As Worksheet
For Each ws In Worksheets
Select Case ws.Name
[Code] .........
View 8 Replies
View Related
Dec 10, 2012
How do I graph this data? I am plotting the test results (Column A is the employee number, B is the Employee name, then each of the questions are Columns D through DX with DY being the total correct by each employee) (Rows are employees and then the Ratio that the question was answered correctly) The goal is to find questions that are routinely answered in correct or that have a problem with the question.
I was hoping to graph the correct and incorrect questions so those that are either high frequency correct, incorrect or not answerd identified I can improve the test.
Example of the spreadsheet. I am using =COUNTIF(D3:D15,"1")/12 for the ratio answered correct - this should also see the ones not answered as incorrect. 1 = correct answer 0 = incorrcet null=not answered
NO
Employee Name
Question
1
2
3
[Code]...
How can I graph these results to answer which questions have a high frequency of incorrect answers? I am thinking some kind of scatter plot.
View 5 Replies
View Related
Feb 11, 2014
I am creating a new sheet that is gathering the data points from the master sheet. These data points are in the same row, but are in every SECOND (=every other) column.
I am struggling with the =INDEX function, which I presume is the best way to gather these data points. I'll then graph this gathered data.
View 3 Replies
View Related
Jun 20, 2014
I am trying to graph dimension measurements that are automatically added as a new worksheet upon completion of a CMM program. Do I need to move all of my data to one work sheet to be able to graph it or is it possible to call the same cell on multiple sheets? Also is there a way to set the graph up so that it updates the information every time a new sheet is added? I have a sample workbook attached that only list one dimension the actual workbook will have many dimensions listed. Sheet 1 is blank and sheets 2-4 contain the information from the CMM.
View 1 Replies
View Related
Dec 1, 2006
I have a graph / time problem
I have a program that is spitting out raw data to me that I would like to be able to put into a graph form in a efficient manner, but I’m not sure the best way to accomplish this.
Here is what I’m working with:
I am getting a list of time date stamps that appear as follows.
11/30/2006 6:02:25 AM
11/30/2006 6:02:36 AM
11/30/2006 6:02:40 AM and so forth.
Each stamp represents a hit to a link.
What I would like to be able to do is graph the number of hits per hour verse the time of day hit. Showing the time on the x & the number of hits per that hour on the y axis.
I can convert the time to decimal, but I still need to find a way to get excel to graph the sum of hits per hour.
View 9 Replies
View Related
Mar 19, 2009
I was wondering what the best way to create a standard deviation graph was? I have a single row of numbers and need to make a stadard deviation graph.
I read somewhere that the analysis toolpak would be useful for this but having installed it i'm not really sure what to do next.
View 7 Replies
View Related
Dec 12, 2009
If I graph a straight line and on several points on this line I want to graph rectangles, how would I calculate the corners of these rectangles.
The rectangles will not always stand 90 degrees on the line. This difference from 90 degrees is in cell L6 The direction of the line is known and the sides of each rectangle is known as well as the center of the rectangle (in meters N or S and E or W). Currently I have the direction of the line in cell L5, the long side of the rectangle in cell L3 and the short side of the rectangle in cell L4. The centers of the rectangles are in rows 1 and 2 of columns M, N, O etc. For instance cell
M1 = 450 (N) and cell M2 = 400 (E). The corners are to be calculated in reference to the values in these cells.
I have been calculating it long hand with a calculator but that takes me a long time and they tend to change things quite regularly. I have to incorporate this with macros that draw the line sition but I have that. Going clockwise from the right top around they would be called corner A, B, C and D.
View 9 Replies
View Related
Mar 26, 2008
I have a workbook with Sheet1 and Sheet2. I’ve programmed information from to be calculated and results inserted into sheet2. A new result every Row. I plan to do a graph taking the X-axis and Y-axis values from Columns A and B respectively in Sheet2. What is the VBA code for dynamic updating of graph? Meaning, I want the graph (a curve) that will have points added to the curve, extending it, whenever a new row is added into sheet2 – Columns A (X-axis) and B (Y-axis). The rows are not defined. It can have 100 to 200 rows or more..
View 6 Replies
View Related
Apr 15, 2009
I was hoping that someone might be able to help me with a current problem that I am facing in my work center. Periodically we have personnel check-in on given day and then later check-out on a specified day. Is it possible to graph/predict/forecast our personnel losses in our department using a chart in excel? Or at least be able to know how many people we will be losing on any week...?
I have attached a sample worksheet of data with columns labeled Name, Gains, Losses.
Columns explained:
1) Name: Name of Incoming Personnel
2) Gains: Date that Personnel Arrived/Checked In
3) Losses: Date that Personnel will Check out
View 7 Replies
View Related
Jul 23, 2012
I'm having some problems with graphing 'blank' cells for my thesis work.
I am filling in data still as I go, and I'm trying to set up my graphs as well so I can easily go in later and modify, fill in more data, or whatever. But all the cells I've formatted as coming back blank are labelled as zeros on the Excel graph. I tried putting in to return #N/A but then Excel is still graphing the cells as zeros. Frustrating. I really do not want to have to recopy over 1000 data points, find the zeros, and cut them out later, I'll have enough to do writing the dang thing. How to modify the formula to have it correctly skip the points when graphing (it's a line graph)?
This is the current formula I'm using. =IFERROR(AVERAGE(C41:Y41), "")
And I put in =IFERROR(AVERAGE(C41:Y41), "#N/A"), which shows the cell as #N/A (which, I'd much rather it be blank, as then I can read the data easier), but it is still graphing as 0.
View 4 Replies
View Related
Feb 13, 2010
I have sheet that is populated with data daily. I wish to create graphs from information contained within a table in this sheet where i can select the start date and end date of the graph possibly from a drop down menu. I will be creating multiple graphs and wish for them all to change when i select the time period.
View 9 Replies
View Related
Jun 21, 2007
I have a lot of seperate data that I'm trying to graph via a macro. I have one macro with a Do Until/Do While loops that runs before I import the data that creates and seperates the data into dynamic named ranges. My goal was to create a macro that graphs the different ranges using another Do Until/Do While loop. You'll see below that I have the ranges concatenated inside the loop so it does just that, graphs each set of data in each worksheet by running the same macro. Below is the code for the specific graphing macro and at the same spot, where the series values are set using the concatenated range name, the error message, "Unable to set the Values property of the series class." I have no idea what is wrong, but my guess is it's probably something simple.
Sub Graph()
Dim Counter As Integer
Dim CounterEQ As Integer
Dim Title As String
Counter = 200
Do Until Worksheets("IndustryEmployment").Cells(1, Head) <> ""
Counter = Counter - 1
Loop
CounterEQ = Counter
Do While Worksheets("IndustryEmployment").Cells(1, Explode) <> ""
GoingTo = Worksheets("IndustryEmployment").Cells(1, Explode)
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("IndustryEmployment").Range( _
"U15:U16"), PlotBy:=xlColumns.......................
View 3 Replies
View Related
Jul 31, 2014
I have a column who's values I would like to graph against many other columns. I was wondering if there was something that would let me do that in one go.
View 1 Replies
View Related
Mar 7, 2014
I am attempting to graph data based on work down in specific work centers to track efficiency. I can download all items that were finished for a certain time frame (yesterday, last month, last year, etc.), but the data only shows up as the item, quantity, and the date of closure. I take that info and copy and paste it to the close out tab of the other work book, where it references another sheet to pull in the time required to complete the task, and the work center that did the work. My problem is when an item has multiple work centers on the same item and then taking that info into a pivot table to graph. This can be done manually, but I was hoping I could create a way to only have to copy and paste the data and have it return the work done.
I have included a shortened version of what I am trying to do, eventually there will be 25000+ items on the data tab, but if I can get this to work, then i assume I can extrapolate.
View 1 Replies
View Related
Feb 10, 2009
I need to graph a function of SIN(X), I did it by writing my x-values in one column with small steps between each value and then calculating the sin(x) value in the column next to it and then graphing the two columns. But this raquires a lot of space on the spreadsheat and it would be convenient to be able to write it on the form y=sin(x). having x defined as a continuous variable. and then plotting the graph
View 2 Replies
View Related
Aug 14, 2014
I'm simply trying to graph my data on a line graph in Excel. see the attached.
The issue is that the spacing on the x-axis is all equivalent. I want the space between the initial and 1st month to be very small while the space between 6 month and 12 month be larger.
So at the end of the day, the line will be kind of crunched down from the beginning and goes up at the 6 month data point.
Is Excel capable of handling such simple request?
View 3 Replies
View Related
Oct 4, 2008
How can I graph merged cells, without graphing gaps or spaces of the skipped cells?
View 12 Replies
View Related
Mar 6, 2009
I have the theoretical set:
year $ %
2003 100 10
2004 120 20
2005 145 30
2006 205 40
2007 300 50
Could I make the year the X-axis and make 2 y-axes on the same graph in excel that each datasets would be correlated to (eg. for money it'd be from 0 to 300 while for percent it'd be from 0 to 50)
View 3 Replies
View Related
Jan 14, 2014
I'm recording a macro, so a line in my graph is a specific green. The marker fill won't retain the green, it always ends up blue. the lines and marker line is green. no matter what color I try the marker fill will always be blue. How to correct this?
View 6 Replies
View Related
Apr 18, 2006
For an economics assignment I would like to graph price elasticity with price on the 'y' axis and units on 'x'. I have not found a way to do this in Excel, can someone assist?
View 6 Replies
View Related
Jun 1, 2011
I have a 5000 line table I am filtering by a few columns, and I'd like to calculate an exponential trendline value.
=INDEX(LINEST(LN(R1059:R1167),W1059:W1167),1)
But I actually don't want all the values from R1059 to R1167 - I want to select only the displayed values (R1059, R1068, R1077, etc). Is there a way to select only display values to use in a formula? The problem is it would be a lot of manual work to select them all - there are 50 or so instances I would have to select 13 manual values.
I am using Excel 2007 on XP.
View 8 Replies
View Related
Jan 5, 2009
i have data on firms for six years as the following :
1
1
1
1
1
1
2
2
2
2
2
2
3
3
3
3
3
3
how can i select the data for first year for each firm.
View 5 Replies
View Related
Mar 26, 2014
I am trying to reproduce a graph similar to this: [URL] ....
How to get the Y axis in the center and two distinct bar charts on either side.
Sample data can be found here: [URL] ....
View 1 Replies
View Related
Jul 31, 2014
I have a database (spreadsheet) which has 3 to 4 dozen columns of data. This data is in Sheet 2. On sheet one I would like to have a form which organizes the data on one sheet from sheet 2. I would like to have a drop down box to select the country. Once country is selected all data to the right of country would then be automatically filled on Sheet 1. see attached example.
View 2 Replies
View Related
Dec 26, 2008
I want to display the first value in a row in a specific column of cells.
Example: Row 5 contains data in columns E thru L
Row 6 contains data in columns G & H
Row 7 has data in only column I
I want to display only the first value in each row in column B according to row. if this is not clear enough please let me know and I will try to clarify.
View 4 Replies
View Related
May 21, 2009
I want (using keystrokes only) to Save As my workbook with the data in a certain cell. However, when I select this cell, and then ctrl + c, it copies the cell (not just the data inside), and so it doesn't let me paste this as the name of the document. Does anyone know how to use keystrokes to highlight the data itself in a selected cell.
View 3 Replies
View Related
May 6, 2014
I have a range c10:c300, i want to select the range where i have data.
E.g. I have data in c10:c50, then select the range c10:c50
View 3 Replies
View Related
