i have a workbook that contains a series of worksheets. the workbook is a master document list. the first worksheet contains no data. the remaining worksheets are arranged so that A1 has the document number, A2 contains the document title, and A3 contains the review date. Id like to create a search macro that can search for the string entered into textbox1. if this is a number, the macro should search A1:A50 of all but the first worksheets in the workbook, and if its text, the macro should search B1:B50 of all but the first worksheets.
the macro should then select the cell containing what was searched. then id like the macro to prompt me and ask if id like to modify the reviewed date to todays date. d like to be prompted to continue searching for other records if they exist, and after that loop has finished to set the focus back to the search textbox.
I'm in the middle of designing a sheet that (amongst many other things) takes the equation of a graph trendline, pastes it, and calculates the various values along it. I can do it by hand, but when I try to record a macro, it doesn't register that I've highlighted the equation and hit copy. Here's the code I'm working with:
Sheets("Graphs").Select ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select Selection.Copy ActiveWindow.Visible = False Windows("Copy of HRA Design calculator1.xls").Activate Sheets("Binder @ max. stability").Select Range("A1").Select ActiveSheet.paste
The problem is line 4 - it can't copy because the text isn't selected.
On top of these graphs, i draw a line (insert a line shape) corresponding to a certain gradient in the data presented. I do not want this line to be exact (i.e. not point to point) as it is best to be drawn by eye so that the general 'gist' of the data can be found. What i would like, using any method available, is to be able to find the gradient of this line i have just drawn which obviosuly must be done taking into account the scale of the graph. The scales of the graph will not be consistent across all applications.
I've created a graph from data and need to add an average/net impact/trend line to the graph. From what I've read this needs to be an XY scatter, however, several efforts later and I can't seem to get it to work for me.
I want to show a stock chart in Open-High-Low-CLose format, then I hope that a trend line can be displayed at the same chart, what can I do? It seems like that stock chart can't be merged with another chart
I have been doing some statistical anaylsis in excel and have been using trendlines within scatter charts quite a bit.
I know excel can display the equation of the line as well as the r-squared on the chart. Is there any way to get the values for the trendline to appear in a cell next to the corresponding chart data? Of course, I can simply enter the formula myself that is given on the chart, but I am wondering if there is a way to do it automatically.
I have included the data below along with the trendline value that I calculated by manually entering the formula from the scatter chart.
Code: YXTrendline Value $78.990.53$79.79 $78.730.53$79.73 $80.550.52$80.88
I need VBA to copy the values from a treadline output box on a chart to a new sheet. The macro recorder produces the code below, but I need to make the 5th line generic so that the code will apply to any workbook. The code below will not run without the "Windows("Test.xls").Activate" statement, which restricts the code to a workbook of a specifc name (Test.xls). How can I make this run for any workbook name?
Sub CopyTreadlineData() Sheets("A").Select ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select ActiveWindow.Visible = False Windows("Test.xls").Activate Sheets("Beta").Select Range("B15").Select ActiveSheet.Paste End Sub
The trend Equation on a chart is of the form y= a+x.(b+c.x) I want to use that equation in a spreadsheet to calculate value of y for the value of x. I can do it manually by inserting the a+x.(b+c.x) in a cell but would like it to be automatic as the a, b, c in the Equation changes regularly as more data is acquired.
I have a simple x y scatter graph, to which i have a trend line running through it, fine.
what i need to do is fine some values according to that trend line. e.g. my X axis goes up to 60 and my Y axis goes to 10, so if i draw accross at 55 from the trend line, i then go down to a number on my Y axis.
I currently have in my equation the trend function to linear extrapolate the date a line would cross either my upper or lower limits (100.1 and 19.9) between 2 data points. However if the 2 data points span both the upper and lower limits (as per the 2 attached examples) then unfortunately the trend function is only able to return the 1 date (where I've specified the x value). Is there any way for the TREND function to trend the 2 values? Do I simply add the 2nd x value to the new x's?
I have attached both the data and their graphs to visually explain my problem : EF Workbook.xlsx
I was asked to make a trend graph showing activation's and deactivation's over time. My boss wanted it to be done automatically when you placed values into a simple excel sheet like the attached image (the graph would be below this).
How can I add missing values to the axis in a pivot chart line chart? I have a numerical X-axis with values such as 0,1,5,8,14, etc. I have another set of Y values that correspond to the X values. If this was an XY-scatter plot I'd be able to plot X vs. Y and connect the dots for "gaps" in the X values. Since it's a pivot chart I cannot use an XY-scatter plot, I can only use a line chart. The line chart doesn't give me the ability to add the "missing" values, so it gives the impression that my data is more closely packed than it is. The data source is external to my spreadsheet, so I cannot add the values before creating the pivot table/chart.
I am using Excel 2007. I have two sets of data. y-series is 58.61, 58.66, 58.71 and 58.76. x-series is 0.8313, 0.8309, 0.8305 and 0.8301.
I am trying to fit a quadratic function to this data. When I plot a curve and fit a trendline I get the coefficients for the equation ax^2+bx+c a=1.3939e-11 b=-125 c=162.5225 The plotted line seems to fit the data fairly well.
However, I want to use the linest function and I am using the formula: =INDEX(LINEST(B2:B5,A2:A5^{1,2}),1,1) I change the index column number as appropriate to get the coefficients: a=-75.23775 b=0 c=110.603763
The coefficients from linest seem to be way out. how I can get the output from linest to be the same as the trendline?
I have created a scatterplot with a trendline and I need a formula to represent the trendline so it can be applied to separate results. How I can create a formula and an R2 value?
I add Data Labels and select the percentages. This shows at Cat 1 having 12.5% of the sales (100 of 800 sales), Cat 2 having 12.25%, and so on..... However, what you don't see is the following data:
So based on 1,088 sales, Cat 1 at 100 is actually only 9.19% of TOTAL sales.
I understand what is happening, the chart is giving the percentage based on the data inputted into the chart.
I did consider adding an 11th category to the chart, which was the total of Catalogues 11-15. Whilst this does give a more accurate percentage figure (as now all sales have been inputted), it makes the chart look ****
Is there a way of either adding the 11th category, and then "hiding" it, so that the chart doesn't display it, but uses it's value to calculate Catalogues 1-10s percentage?
BTW: The reason I ask is that I could be dealing with anywhere from 30 to over 100 catalogues. So it's not ideal showing the others in 1 chart.
I'm trying to determine the percentage rate of change in a trendline. I've got a sales chart with 90 days worth of sales figures. I can see that the trendline is trending down, but I'm not sure exactly how much.
If you take the first data point in a trendline and subtract it from the last data point and then divide that by the first it should give you the percent change. Excel must be able to do that math for you but I can't even figure it out to save my life. I can't even figure out how to display the first and last data point much less the "rate" of change I'm looking for.
i have a function in a cell (that works) to extract coefficients from a range of cells in a workbook:
VB: =INDEX(LINEST(CP25:CP27,CQ25:CQ27^{1,2}),1)}
i have variables for cp25:cp27 and cq25:cq27 already defined in my vba code. the values for these in the case i am working on are as follows (returns 110.5):
1) how to do this function in VBA only - this is part of a UDF and cannot have any helper cells 2) how to refer to 560,570,580 as a 'range'. is there a way to put these six variables into my ranges for later processing?
All of the google searches i have deal only with linear regression, taking from existing graphs, or say to just use the function i have above.
I have tried
VB:
Var = Application.WorksheetFunction.LinEst(Sheets("references").Range("CP25:CP27"), Sheets("references").Range("CQ25:CQ27^{1,2}"), 1) [COLOR=#333333][/COLOR]
but return #value! errors. when i remove the ^{1,2} portion, i do return a value but it is incorrect (returns 160), what is the correct syntax for adding in the ^{1,2}? if you do that would be fantastic, but brings me back to issue #2 in that i need to refer to my variables in the vba code and not this range (as they will eventually be going away).
I'm looking to generate a chart out of a table of data. I want the Y-axis to show a percentage return on an investment.
On the x-axis I want to have a range of scenarios from a market average return of -40% to +40%. Obviously, I wish for 0% to sit in the middle of the x-axis.
I'd want to have 3 lines observable in the chart. I'd be looking to build the above out of a table almost exactly like this:
I have some data where I am plotting cumulative M events versus time (blue). I would like to make a tick mark, or dot or something to indicate when event N (pink) occurs on the blue line. Imagine that I am measuring eating (M) versus time and that I want to show on that line the points when food is delivered (N). this is the plot I desire (ive shown some ticks and dots as examples, how can i do either one?): http://www.yousendit.com/transfer.ph...2F360E1F246391 and this is my data/current plot: http://www.yousendit.com/transfer.ph...27974E102C0620
I have a time series chart ( dates on x axis , with the data values on y axis ). It is a line chart. So column A has the dates, and column B has the values. Both have length 100. Now, column C can be considered to be the "flag" column. If a cell in Column C has the flag set to true , then the corresponding date/data values in the chart needs to be highlighted or formatted differently
I tried playing around with conditional formatting of x-axis but could not make any progress. Do I need to create a new data series with the C column?
Also given that there are 100 date values, I dont want all x-axis points to be listed on the chart. I guess excel by default shows the date points as the user increases/decreases size. But I dont want the specifically formatted date points to be skipped .
now I thought about recording the macro, but it's an offset and if you click the chart, then it'll mvoe somewhere else even though the charts are always placed in the middle when generated. So I think I need to place it in a specific range. I want to use XY coordinates or range labels or something to that effect but would like something to this affect:
With Charts.Add .ChartWizard source:=Worksheets("sheet1").Range("a1:a20"), _ gallery:=xlLine, title:="February Data" End With
I got that in VBA help, but I'm not using Chart wizard, my code just creates the chart from the data on its own with specific chart type. So anyway, how would I make it put each of my five charts for the 25 people in the same place for every person. Performance Errors etc etc etc all will go in the right place everytime, regardless if you start with the page scrolled. I should do range right, but how do I change this macro to do it without chart wizard...just a chart that's already created and named. QUESTION: How do I place charts that are created from a data source onto a specific area of one worksheet, and those charts are always in the same place regardless of the person'a name.
I'm working on an assignment that pulls information for different countries and I'd like to know how to go about placing the charts on a specific area. Any help would be greatly appreciated!
The concept:
With each click of a country, the country's information will be pulled from a 'data dump' that I created in the same file. The map and flag are also shown. Some of that information is just using lookups, or a combination of lookups and code, and the hardest part are the GRAPHS - which I'm having a really hard time placing and adjusting accordingly.
The problem:
As of now the charts are automatically filled as the user clicks a new country, but all of the charts are placed in weird locations, but I'd like for them to be placed in the specified ranges above.
I am trying to format my x-axis to list only the labels that have corresponding data points, but at the same time maintain appropriate number spacing. For example, My x-axis range is 0-90; however, I only want tick marks at 0, 5, 13, 40, 85.
