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 RepliesIs there a way to select cells with formulas for a top row and then fill down the formulas for these non adjacent, non-contiguous, etc. columns? I can paste a single formula, but not varying.
i.e. Cells A1, D1, and G1 have my formulas, and I want to identify that they have formulas in the first place and then drag down to let's say A10, D10, and G10. I will not know which columns contains cells before hand, and the given formulas are not fixed
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] ....
In VBA, I just want to use an object within a formula that represents rows within a range of cells.
Here's what I mean. I create an variable named "additup":
additup = 2 'This value represents that row number I will want in the following messed up formula:
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(RC[-7]),RC[-7]/MAX(range(P(additup:Padditup+6000")),"""")"
Above, the formula checks if the cell that is 7 columns to the left is a number, and if so, divide it by the max value within the range (Col P, Row= additup) : (Col P, Row= additup+6000).
Clearly my problem is VBA syntax.
For our monthly report we would like to make a sum formular where the end column is a variable, so it can be updated one time instead of updating every formular. When I try a text formular it doesn't calculate but only show the text string. ="=sum(b5:"& a1 & "5)" so I can enter c in cell a1 for 2 comumn/month summation.
View 5 Replies View RelatedI am looking for formulas to work out what is on the worksheet attached
View 2 Replies View RelatedWhat I'm trying to do is use a variable NumberOfRows (which is defined as an integer) in this following code, but the problem I get is that the NumberOfRows isn't being inserted.
Let's assume that NumberOfRows = 15.
What I would expect to be in cell B1 is =sum(H5:H15), but what is getting placed there is =sum(H5:HNumberOfRows) which of course doesn't give me anything. I'm sure this is an easy syntax thing, but I just can't seem to get the right combination.
Range("B1").Formula = "=Sum(H5:H&NumberOfRows)"
I am using heavily formula based modelling workbooks and I need to know how to search for variables (inputs) in these workbooks as they contain many thousand rows and it would take ages to go through line by line looking for these variables
View 3 Replies View RelatedI am trying to have the formula =( SUMIF(S2:S125,">0",S2:S125))/(COUNTIF(S2:S125,">0")) Put into cells through vba. What I did to get the formula is typed it into an excel cell to find the average of a group of cells that do contain blank cells. The formula brought out the proper results. So all I did is put the formula into vba and changed the appropriate parts. The range will not be the same of course, but there is what I have.
ActiveCell.Formula = "=(SUMIF(" & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & ","">0""" & "," & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & "))/(COUNTIF(" & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & ","">0""" & "))"
When I show a msgbox for ActiveCell.Formula (Msgbox activecell.formula), it shows me the formula as above - =(SUMIF(S2:S125,">0",S2:S125))/(COUNTIF(S2:S125,">0")) Except instead of the s:ranges, it shows $L2:$L125 (which is correct). The quotes do show up around the criteria in both the sumif and countif. I keep receiving an error. I put a msgbox err.description & ", " err.number dialog in. The error comes up as ", 0" (no quotes).
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.
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 RelatedI 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.
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.
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.
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.
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 RelatedI 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
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.
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.
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
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 RelatedI 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.......................
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 RelatedI 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.
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] .........
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.
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?
How can I graph merged cells, without graphing gaps or spaces of the skipped cells?
View 12 Replies View RelatedI 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)
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