how to get my LINEST function to work properly. Now, what I'd like to do is have it reduce or increase the order of the polynomial until R^2 equals 1.0, or the polynomial reaches 6th order, whichever comes first.
Can I do this with a bunch of nested IF ELSEIF statements, or is there a cleaner way to do this?
I have the following loop that should look through my rows of data for the Value "Y" in column T, for those rows that contain "Y" it should update column K with the variable xdate. For some reason it doesn't seem to work, it changes the first entry it finds and then seems to crash, I have to use CTRL+BREAK to stop it.
View 3 Replies View RelatedI am trying to loop through a range, checking for a cell to contain one of two characters and then put a value in a cell to the side (well 3 away) of it. Im getting an error for Type mismatch on line:
VB:
If CellContent = "h" Or "v" Then
the data in the cell is an along the lines of '12.34.56.43 som-thi-vh-ng1'
VB:
Dim MyRange, MyCell As Range
Dim CellContent As String
Sheets("Sheet1").Activate
[Code]....
I have a code like this below
Sub Copydata()
Dim emre As Integer
For emre = 100 To 121
Sheets("CP TB").Select
ActiveSheet.Range("$B$8:$G$10000").AutoFilter Field:=3, Criteria1:=emre
Range("E8:F8").Select
[Code] .....
I want to skip values when the selection is a value I choose. I tried to put a "If" statement but I couldn't make it work.
The IF statement will check the selection after red colored code. if cell value is "a" then it will go for Next emre.
I am getting the Run-time error '380'. Could not set the value propery. Invalid property value.
I have a user form for data entry purpose which is working fine. Now, I am making a code for data editing purpose which pulls out data from the worksheet into the user form.
I am able to do this for text boxes and combo boxes that are not linked with each other. However I am getting a problem in the following case; I have a combo box and a text box that are linked to each other by combo box _change code which is below which may be the reason for the error I am getting for the next code...
Code:
Private Sub cmbColor_Change()
If cmbColor.Value = "Other" Then
lblColor.Visible = True
[Code]....
I have the following code which previously was showing an error on the Do While line. It was suggested that I change the line too Range("B2").Text. However, the loop is not deleting the rows based on "#N/A". Can anyone offer any suggestions?
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess .....
So here is my problem:
how do you repeat a write statement four time
form 1234 to 1234123412341234
If ComboBox1.Text = " Market 1" Then
StoreNumList = Array ("66", "67", "107", "222") 'obviously array string is much longer than this
For x = LBound(StoreNumList) To UBound (StoreNumList)
StoreNum = StoreNumList(x)
ComboBox1.value = StoreNum
Call btnOK_Click_Process
Next x
Program runs fine first step through. It calls the btnOK_Click_Process sub routine just fine. However on second and subsequent For/Next cycles it does not call btnOK_Click_Process.
I am having an issue getting an IF statement to work inside a DO loop. I have Microsoft Excel 2007 and Windows 7 Entreprise.
In my Excel spreadsheet, I have in column B a list of keywords and in column C the category to which each keyword belongs. For instance, the keyword "boot" goes withe the category "Shoes", "scarf" goes in "Accessories", etc.
In another column, I have a list of product descriptions (like "Black Boots" or "Silk Scarf") and I would like to assign the relevant category to each product. I thought to do this by creating a new function in VBA. This function would go through the whole list of keywords in column B, checking if the keyword is included in the product description. If it is, the function's result would be the relevant category, and if it is not, the function would search the next line in column B, until a relevant keyword is found, or until the end of the list.
Here is the code I have written so far:
Code:
Function SegmentSearch(Item)
Dim i As Integer
i = 1
Do
i = i + 1
If Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(Sheet5.Cells(i, 2), Item)) = "TRUE" Then SegmentSearch = Cells(i, 3)
Loop Until Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(Sheet5.Cells(i, 2), Item)) = "TRUE"
End Function
Without the loop, the function works fine (if "Item" happens to include the first keyword in the list) but as soon as I try putting the loop I get a #VALUE! error.
My nested loop works as intended without If statements, ie it counts non blank cells in a series of 17 ranges (this loop is called the CountRangesOnEachRow loop) AND then drops down a row and repeats (this loop is called the RowNo loop). The problem is that once my first If statement occurs the RowNo loop fails and it stops at the first row.
The first If statement is designed to do the following:
1. Tests to see if A4 has data. If yes, copy cell contents (pupil's name) to Sheet(2).Range("StudentName")
2. If empty skip the countranges loop and move down a row to A5.
3. Cells A4:A35 need to be tested for data in this manner.
I've included a sample workbook & code with the IF statement comment blocked so you can see what it should do AFTER it tests the A column for data. The macro should end after row 6 as A6:A35 are all blank.
Setting value in For Each statement stars infinite loop. I am trying to get the following script to work
View 2 Replies View RelatedI think the problem is the order in which I have the "next" loops. I would like to evaluate the if statement below over a range of cells
Sub Analysis()
Dim i As Integer
Dim x As Integer
Dim y As Integer
Dim k As Integer
Dim TheLast As Integer
TheLast = Sheets("Name").Range("c" & Rows.Count).End(xlUp).Row
For i = -7 To -26 Step -1
For x = 24 To 43
For y = 14 To 43
For k = 16 To TheLast.................
I have a table something like this (much bigger in reality)
1 5
2 10
5 15
7 #N/A
9 20
12 25
I need to interpret the above data with the linest function, but for a variety of reasons i cant delete the error square. How can i get the LINEST to ignore the #N/A and the 7? otherwise it returns an error.
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?
My "calculated" polynomial is not the same as the Trend line on the graph. The one on the graph seems to be correct.
Basically, I am using the LINEST function to create a polynomial equation inorder to extrapolate data. And also with a given equation, and a given y, find x.
I'm trying to implement the linest formula in a programming language for my coursework.
I've looked on excel help but it only explains on how the function selects data.
how to the values are calculated and the steps?
I am calculating variables in Vba and want to find their co=effiicients in the for y=a +bx +cx^2. This works great if I output my variables to excel and then pick up this range however as I am generating the variables in vba and have an array of variables I would rather not have to keep spitting them out to excel then picking them up again. The three important lines in my code are:
c(nFix - j) = WorksheetFunction.Index(WorksheetFunction.LinEst(inpv, Application.Power(inps, Array(1, 2))), 1)
b(nFix - j) = WorksheetFunction.Index(WorksheetFunction.LinEst(Range(Vc), Application.Power(Range(SPd), Array(1, 2))), 1, 2)
a(nFix - j) = WorksheetFunction.Index(WorksheetFunction.LinEst(Range(Vc), Application.Power(Range(SPd), Array(1, 2))), 1, 3)
As you can see for c I am referring to inpv and inps these are arrays. I have tried using inpv(), etc but no luck
b and a work great, here I am reffering to ranges Vc and SPd. As I am going through this a lot of times it seems rather inelegant not to be able to do the whole thing in vba - i would rather not use the dump to spreadsheet then pick up again approach!
is there a way to use LINEST in a way in which the x values are unknown and the y values are known? The opposite of how the function usually runs...?
View 2 Replies View RelatedI am trying to obtain the 3 coeffs from a polynomial trendline equation.
The equation produced on the chart is
y = 3.1123x2 + 135.34x + 124.9 from the below info
X Y
1.0 250
2.5 500
5.0 890
7.5 1290
10.0 1800
But when I enter this array formula
{=LINEST(B2:B6,A2:A6^{1,2})}
I get different results.
-48.98,375.89,214.98
I can do least squares fitting utilizing LINEST and a worksheet. (See attachment.)
I would like to do the same calculations entirely within VBA.
I can specify the values as arrays within VBA :
Sub linest2VBA()
'
' linest2VBA Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Dim dXvalSqd(4), dXval(4), dYval(4), dFirstRegStat As Double
Dim i As Integer
Second Question:
Is there a way to get the remaining regression statistics within VBA by referencing only the three arrays: dXvalSqd, dXval, and dYval?
I need to know how to selec no-adjacent columns for the known_x's in the linest function:
Example:
'- A B C D E
1) 80 63 9 3 26
2) 80 63 9 3 21
3) 74 65 7 2 30
4) 74 65 7 2 20
5) 63 65 9 3 16
=LINEST(A1:A5,KNOW_X'S,TRUE,TRUE)
Is there any way to select for instance: column B and E as known_x's?
I heard about something where we can select total area and then choose which columns should be considered via "{column1, colum2}"..
That is all is missing for me to finish my project!!! Pls your help!!!
I have built a user defined function called: portAlpha.
Code:
Function portAlpha(assetCode As String, indexCode As String) As Double
Dim assetCol As Long
Dim indexCol As Long
Dim assetData As Range
Dim indexData As Range
With Sheets("Data")
[Code] ......
I am struggling to get this to work though. Everytime I step over this piece of code:
Code:
portAlpha = Application.WorksheetFunction.LinEst(Range("M4:M63"), Range("N4:N63"), 1, 1)
I get a "Type Mismatch Error". All the values in the range are numbers, and LINEST should return a double, so I am puzzled as to why there is a mismatch...
I have hardcoded the range into the formula just to try and get it to work. Eventually this will be two variables and will look like this:
Code:
portAlpha = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(assetData, indexData, 1, 1),1, 1)
I am trying to use the LinEst function in a program. There are some good examples on the web but they all refer to a range in the format “A1:A45”. Is there a way to refer to a column in an existing array instead?
For example (thanks to Shg for this code): ....
I have set up regression in excel using the linest array function which automaticly updates whenever the data it is references changes. Currently the formula in K3 =
=LINEST(K8:K50,M8:N50,TRUE,TRUE) .
Only the Y range in column K changes and then the formula automaticly recalculates the regression parameters. However sometimes the range of valid data changes with each new update so currently K8 to K50 contains valid values and so the formula works. But if the range of valid data now ends at say K45, and the remaining rows (K46- K450) contains #DIV/0!. In this case the formula does not work and I need to go in manualy into K3 and reselect the range to get it to work (in the baove case to =LINEST(K8:K45,M8:N45,TRUE,TRUE)
Is there anyway I can get the formula to update the range (the number of rows to use) in the calculation automaticly so I don't have to go into the formula in K3 and change the range manually everytime the data changes? Basicly I like to use all rows in column K (from K8 to K50) where there is a values and ignore cells with #DIV/0!
I'm guessing the easiest way to do this would be to set up a dynamic range of some sort that feeds into the linest array function so that the end row reference for columns K and M & N changes depending on where the first #DIV/0! occurs in column K.
I have instances where my Y variables sometimes contain a zero in the data and i need a formula to skip over that data point in the LINEST calculation.
Y variables are in Row 1, Columns A:E
X variables are in Row 2, Columns A:E
The following formula is returning a #VALUE! error: .....
I have a worksheet built that obtains info from Sheet1(ACV) and enters it into Sheet2(Payment1), however when I run the process, only Line 10 fills and then it stops. I need to loop the first statement of the code, so that it fills all of column 1 until there is no info to fill then I assume the rest of the code will fill in the worksheet accordingly.
View 3 Replies View RelatedI'm using Excel 2003 and I'm trying to make my LINEST function and the source cells for a scatter graph use the cell range specified in another cell in the document. I figured out that it was simple to do the following:
I250 contains: 10, shows: 10
I251 contains: ="I"&I250, shows I10
But I can't figure out how to do this in a larger function. I've tried a few things but none of them work. Here is an example where I want it to do a LINEST with y values in cells Ja - Jb and x values in cells Ia - Ib (where a and b are integers specified in cells I250 and J250 respectively):
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.
Why it stops after one file when using "Application.Run..." to call the macros?
NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.
Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long
[Code]....