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.
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.
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 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 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
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).
How do I get to see the equation behind Excel Functions? For instance, the result of pmt() is not making sense to me, I'd like to see what this function is doing and how it is calculating its value. Trial and Error is rather time-consuming and tedious.
Lets say I write an equation on my sheet such as: ='A1'!$H422*AH$138
but instead of hard coding the A1 sheet name, I want that to be a value that I can put in a different cell (lets say cell C1) on my same sheet in such a way that it will reference the value in my other cell (Cell C1) and put its value into that equation.
So if I make the value in C1 = A99 then the equation becomes ='A99'!$H422*AH$138
i am plotting graphs which i need the equation of the line from. i have been manually typing the x^3, X^2, X^1 and X^0 values into 4 cells which is then used elsewhere, but wanted to know if its possible to get some code to do that for me?
Below is a nonlinear model, together with calibration data for which I would like to find the parameters Ro, Kc, a and b.
R = Ro + Kc * (1/(1 + exp(-(a + b*lnC))
Calibration Data
C R 0.00.3347 4.80.4201 7.70.5141 11.20.6177 14.40.6887
Given the calibration data above, how can I calculate or at least estimate these parameters? I do not want to use an add-in program, like Solver, in Excel. I do, however, want to be able to use any existing functions, if necessary, in Excel. i'd appreciate it if someone can walk me through the steps.
The equation is basically: 10^[A/20]+10^[B/20]+10^[C/20]. Now imagine that there is no input value for C. I need to make it so that it won't add the bit I've highlighted in green.
I will like to ask, if there is a way to get in a cell the result of a simple equation without having to copy the contents of that cell with a = in front for example if i have in one cell
How to add an equation to a drop down selection if you take a look at the picture in the link supplied, what I want to do is: I want the calculator to take the number in D12 then if the drop down box under it the selection is (as it is) too high then subtract 50 (k11) from D12 and display the answer in D14. I have listed the 3 drop down choices in column L, Lap 2 I would do exactly the same and so on [URL] .........
I am trying to calculate the equation of a curve which best fits some data. On the x-axis is date and y-axis the readings. The curve is quadratic, so on the chart I am fitting a 2nd order polyomial and displaying the equation. To prove that the equation the trendline gives me is reasonable, I am applying the equation on the x parameter to get the y parameter estimate and looking to see the error between predicted and actual - see attached.
The equation and the output from it are clearly rubbish, even though on the graph the trendline is a reasonable fit - see attached. Has anyone any ideas why? I think it has something to do with using a date on the x-axis but no idea why. Is there a way to correct for this? I also tried using the following I to calculate the coefficients which came up with a reasonable forecast (although linest should only be used for linear data): =LINEST(C3:C15,B3:B15^{1,2,3})
I'm familiar with 2d graphs, trendlines, and regression equations. Now I have some tabular data that has 2 input variables and a result. As with my 2d data, I would like Excel to create a polynomial equation of z from the x and y inputs. Can Excel do this, or do I need a plug-in or another software package?
