Help correcting macro

[michaelsmith559]

I need help correcting a macro. Below will be 3 macros. The macro called "bestmlr2" is the one I am trying to duplicate. The macro "bestmlr2" allows me to enter variable transformations as expressions and evaluates them for linear regression models. It works great. The macro called "originalbestpolyreg" is the macro for the original polynomial regression. The difference is that you cannot enter expressions like in "bestmlr2". The macro "bestpoly1" is my attempt at modifying "originalbestpolyreg" to use expressions. I pretty much copied elements from "bestmlr2". However, it is giving an error saying "next without for" for the line "Here: iX" I cannot figure out why. It looks as all loops have been closed, but I cannot find where I missed it. Here are the codes. Thanks for any help.

Option Explicit
Option Base 1
Function ExFx(ByVal sFx As String, ByRef X() As Variant, _
ByRef Y() As Variant, _
ByVal I As Integer, ByVal NumIVars As Integer) As Double
Dim J As Integer
' replace Xnn starting with the higher indices just in case there
' are more than 9 variables.
sFx = UCase(sFx)
For J = NumIVars To 1 Step -1
sFx = Replace(sFx, "X" & J, "(" & X(I, J) & ")")
Next J
sFx = Replace(sFx, "Y", "(" & Y(I, 1) & ")")
ExFx = Evaluate(sFx)
End Function
Sub BestMLR2()
Const MAX_ERRORS As Double = 1000000# ' initial max error value
Dim ErrorCounter As Double, MaxErrors As Double, sMaxErr As String
Dim NumIndpVars As Integer ' number of independent variables
Dim TN As Integer ' total number of variables = NIV+1
Dim N As Integer ' number of data points
Dim MaxTrans As Integer ' max transformations
Dim MaxResults As Integer ' max results
Dim Col1 As Integer, Col2 As Integer, Col3 As Integer
Dim Col4 As Integer, Col5 As Integer
Dim I As Integer, J As Integer, K As Integer
Dim M1 As Integer, M As Integer
Dim VarIdx As Integer, Low As Integer, Hi As Integer
Dim TransfMat() As String, sFx As String
Dim CurrentTransf() As String, CountTransf() As Integer
Dim NumTransf() As Integer ' number of transformations
Dim Y() As Variant, X() As Variant
Dim Yt() As Variant, Xt() As Variant
Dim vRegResultsMat As Variant, WS As Worksheet, newws As Worksheet
Dim F As Double, Rsqr As Double, xval As Double
Dim fMaxCount As Double, fCount As Double, fMilestone As Double
Dim dt1 As Date, dt2 As Date, mresults As Variant
Dim yrng As Range, xrng As Range, xcount As Long, a As Long
Dim lastc As Long, Ty As Range, Tx As Range, xpaste As Long
Set yrng = Application.InputBox(prompt:="Select Y Range", Type:=8)
Set xrng = Application.InputBox(prompt:="Select X Range", Type:=8)
Set WS = Workbooks("Multiple Linear Regression using Transformations.xlam").Sheets("Variable Transformations")
Set Ty = WS.Range(WS.Range("A2"), WS.Range("A2").End(xlDown))
Set Tx = WS.Range(WS.Range("B2"), WS.Range("B2").End(xlDown))
xcount = xrng.Columns.Count
mresults = InputBox("Maximum Results to Display")
On Error Resume Next
Set WS = Sheets("MLR1")
If Err.Number <> 0 Then
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MLR1"
Else
End If
Sheets("MLR1").Activate
    With Sheets("MLR1")
        .Cells.ClearContents
        .Cells(1, 1).Value = "Number of X Variables"
        .Cells(2, 1).Value = xcount
        .Cells(3, 1).Value = "Max Results"
        .Cells(4, 1).Value = mresults
        .Cells(1, 2).Value = "Y"
        .Cells(2, 2).Resize(yrng.Rows.Count, yrng.Columns.Count).Cells.Value = _
            yrng.Cells.Value
            For a = 1 To xcount
                Cells(1, a + 2).Value = "X" & a
            Next a
        .Cells(2, 3).Resize(xrng.Rows.Count, xrng.Columns.Count).Cells.Value = _
            xrng.Cells.Value
    End With
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(1, lastc + 2).Value = "Transform Y"
Cells(2, lastc + 2).Resize(Ty.Rows.Count, Ty.Columns.Count).Cells.Value = _
            Ty.Cells.Value
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
    For a = 1 To xcount
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
        Cells(1, lastc + 1).Value = "Transform " & "X" & a
    Next a
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(2, lastc - xcount + 1).Resize(Tx.Rows.Count, Tx.Columns.Count).Cells.Value = _
    Tx.Cells.Value
xpaste = 1
    For a = xpaste To xcount - 1
        Range(Cells(2, lastc - xcount + a), Cells(Rows.Count, lastc - xcount + a).End(xlUp)).Select
        Selection.Copy Selection.Offset(0, 1)
        Selection.Offset(0, 1).Select
        Selection.Replace "X" & a, "X" & a + 1, xlPart
    Next a
Cells(1, lastc + 2).Value = "F"
Cells(1, lastc + 3).Value = "Rsq"
Cells(1, lastc + 4).Value = "Intercept"
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
    For a = 1 To xcount
        Cells(1, lastc + a).Value = "Slope" & a
    Next a
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(1, lastc + 1).Value = "Transform " & "Y"
    For a = 1 To xcount
        Cells(1, lastc + a + 1).Value = "Transform" & "X" & a
    Next a
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
dt1 = Now
ErrorCounter = 0
MaxErrors = MAX_ERRORS
NumIndpVars = [A2].Value
MaxResults = [A4].Value
TN = NumIndpVars + 1
Col1 = 2 ' first column of data
Col2 = Col1 + TN + 1 ' first column of transformations
Col3 = Col2 + TN + 1 ' first column of results
Col4 = Col3 + TN + 1 ' first column of tranformatioons
Col5 = Col4 + TN - 1 ' last column of transformations
Range(Cells(2, Col3), Cells(1 + 2 * MaxResults, Col3 + 3 * TN)).Value = ""
Range(Cells(2, Col3), Cells(1 + MaxResults, Col3 + 1 + TN)).Value = 0
MaxTrans = Range(Cells(2, Col2), Cells(1, Col2)).CurrentRegion.Rows.Count - 1
ReDim NumTransf(TN), TransfMat(TN, MaxTrans), CurrentTransf(TN)
ReDim CountTransf(TN)
fMaxCount = 1
For I = 1 To TN
J = 2
Do While Trim(Cells(J, Col2 + I - 1)) <> ""
TransfMat(I, J - 1) = Cells(J, Col2 + I - 1)
J = J + 1
Loop
NumTransf(I) = J - 2
fMaxCount = fMaxCount * NumTransf(I)
Next I
N = Range("B1").CurrentRegion.Rows.Count - 1
Y = Range("B2:B" & N + 1).Value
X = Range(Cells(2, 3), Cells(N + 1, 2 + NumIndpVars)).Value
Yt = Range("B2:B" & N + 1).Value
Xt = Range(Cells(2, 3), Cells(N + 1, 2 + NumIndpVars)).Value
' set the initial transformations
For I = 1 To TN
CurrentTransf(I) = TransfMat(I, 1)
CountTransf(I) = 1
Next I
fCount = 0
fMilestone = 0.1
Do
On Error GoTo HandleErr
For I = 1 To N
DoEvents
If fCount / fMaxCount > fMilestone Then
DoEvents
Application.StatusBar = "Processed " & CStr(fMilestone * 100) & " %"
If fMilestone < 1 Then fMilestone = fMilestone + 0.05
End If
' sFx = CurrentTransf(1) '[A5].Value
Yt(I, 1) = ExFx(CurrentTransf(1), X, Y, I, NumIndpVars)
For J = 1 To NumIndpVars
'sFx = CurrentTransf(J + 1) ' Range("A" & M).Value
Xt(I, J) = ExFx(CurrentTransf(J + 1), X, Y, I, NumIndpVars)
Next J
Next I
' perform the regression calculations
vRegResultsMat = Application.WorksheetFunction.LinEst(Yt, Xt, True, True)
Rsqr = vRegResultsMat(3, 1)
F = vRegResultsMat(4, 1)
' check if F > F of last result
If F > Cells(MaxResults + 1, Col3) Then
xval = fCount / fMaxCount * 100
xval = CInt(100 * xval) / 100
Application.StatusBar = "Processed " & CStr(xval) & " %"
M1 = MaxResults + 1
' write new results to row M
Cells(M1, Col3) = F
Cells(M1, Col3 + 1) = Rsqr
For I = 1 To TN
Cells(M1, Col3 + I + 1) = vRegResultsMat(1, TN - I + 1)
Next I
For I = 1 To TN
Cells(M1, Col4 + I) = CurrentTransf(I)
Next I
Range(Cells(2, Col3), Cells(MaxResults + 1, Col5 + 1)).Select
Range(Cells(2, Col3), Cells(MaxResults + 1, Col5 + 1)).Sort Key1:=Range(Cells(2, Col3), Cells(MaxResults + 1, Col3)), Order1:=xlDescending
End If ' If F > Cells(MaxResults + 1, Col3)
GoTo Here
HandleErr:
fCount = fCount - 1
ErrorCounter = ErrorCounter + 1
If ErrorCounter > MaxErrors Then
If MsgBox("Reached maximum error limits of " & ErrorCounter & vbCrLf & _
"Want to stop the process?", vbYesNo + vbQuestion, "Confirmation requested") = vbYes Then
Exit Sub
Else
sMaxErr = InputBox("Update maximum number of errors", "Max Errors Input", MaxErrors)
If Trim(sMaxErr) = "" Then
MsgBox "User canceled calculations process", vbOKOnly + vbInformation, "End of Process"
Exit Sub
End If
MaxErrors = CDbl(sMaxErr)
ErrorCounter = 0
End If
End If
Resume Here
Here:
' ---------------------------------------------------------
' ---------------------------------------------------------
' ------------ Simulate Nested Loops ---------------------
' ---------------------------------------------------------
' ---------------------------------------------------------
For VarIdx = 1 To TN
DoEvents
If CountTransf(VarIdx) >= NumTransf(VarIdx) Then
If VarIdx < TN Then
CurrentTransf(VarIdx) = TransfMat(VarIdx, 1)
CountTransf(VarIdx) = 1
Else
Exit Do
End If
Else
CountTransf(VarIdx) = CountTransf(VarIdx) + 1
CurrentTransf(VarIdx) = TransfMat(VarIdx, CountTransf(VarIdx))
fCount = fCount + 1
Exit For
End If
Next VarIdx
Loop
On Error GoTo 0
dt2 = Now
[A6].Value = "Start"
[A7].Value = dt1
[A8].Value = "End"
[A9].Value = dt2
Range(Cells(1, 1), Cells(Rows.Count, lastc).End(xlUp)).EntireColumn.AutoFit
Range(Cells(1, 1), Cells(Rows.Count, lastc).End(xlUp)).HorizontalAlignment = xlCenter
Application.StatusBar = "Done"
MsgBox "Start at " & CStr(dt1) & vbCrLf & _
"End at " & CStr(dt2), vbOKOnly + vbInformation, "Success!"
End Sub

Option Explicit
Option Base 1
Sub OriginalBestPolyReg()
Const MAX_ERRORS As Double = 1000000# ' initial max error value
Dim ErrorCounter As Double, MaxErrors As Double, sMaxErr As String
Dim NumIndpVars As Integer ' number of variables
Dim PolyOrder As Integer ' polynomial order
Dim TN As Integer ' total number coefficients
Dim N As Integer ' number of data points
Dim MaxTrans As Integer 'Max transformations
Dim MaxResults As Integer ' max results
Dim Col1 As Integer, Col2 As Integer, Col3 As Integer
Dim Col4 As Integer, Col5 As Integer
Dim I As Integer, J As Integer, K As Integer
Dim M1 As Integer, M As Integer
Dim iY As Integer, iX As Integer
Dim TransfMat() As Double
Dim CurrentTransf() As Double, CountTransf() As Integer
Dim NumTransf() As Integer ' number of transformations
Dim Y() As Variant, X() As Variant
Dim Yt() As Variant, Xt() As Variant
Dim Xtp() As Variant
Dim vRegResultsMat As Variant
Dim F As Double, Rsqr As Double, xval As Double
Dim ShiftX As Double, ShiftY As Double
Dim ScaleX As Double, ScaleY As Double
ErrorCounter = 0
MaxErrors = MAX_ERRORS
NumIndpVars = 2
PolyOrder = [A2].Value
MaxResults = [A4].Value
TN = PolyOrder + 1
ShiftX = [A6].Value
ScaleX = [A8].Value
ShiftY = [A10].Value
ScaleY = [A12].Value
Col1 = 2  ' first column of data
Col2 = 5  ' first column of transformations
Col3 = 8  ' first column of best transformations
Col4 = 10 ' first column of results
Col5 = Col4 + TN + 1  ' last column of results
Range(Cells(1 + Col3), Cells(1, 50)).Value = ""
Cells(1, Col3) = "Transf Y"
Cells(1, Col3 + 1) = "Transf X"
Cells(1, Col4) = "F"
Cells(1, Col4 + 1) = "Rsqr"
For I = 0 To PolyOrder
Cells(1, Col4 + 2 + I) = "A" & I
Next I
Range(Cells(2, Col3), Cells(1 + 2 * MaxResults, Col4 + 3 * TN)).Value = ""
Range(Cells(2, Col4), Cells(1 + MaxResults, Col4 + 1 + TN)).Value = 0
MaxTrans = Range(Cells(2, Col2), Cells(1, Col2)).CurrentRegion.Rows.Count - 1
ReDim NumTransf(NumIndpVars), TransfMat(NumIndpVars, MaxTrans), CurrentTransf(NumIndpVars)
ReDim CountTransf(NumIndpVars)
For I = 1 To NumIndpVars
J = 2
Do While Trim(Cells(J, Col2 + I - 1)) <> ""
TransfMat(I, J - 1) = Cells(J, Col2 + I - 1)
J = J + 1
Loop
NumTransf(I) = J - 2
Next I
N = Range("B1").CurrentRegion.Rows.Count - 1
Y = Range("B2:B" & N + 1).Value
X = Range(Cells(2, 3), Cells(N + 1, 2 + PolyOrder)).Value
Yt = Range("B2:B" & N + 1).Value
Xt = Range(Cells(2, 3), Cells(N + 1, 2 + PolyOrder)).Value
ReDim Xtp(N, PolyOrder) ' polynomial data matrix
For iY = 1 To NumTransf(1)
CurrentTransf(1) = TransfMat(1, iY)
For iX = 1 To NumTransf(2)
CurrentTransf(2) = TransfMat(2, iX)
On Error GoTo HandleErr
For I = 1 To N
DoEvents
If CurrentTransf(1) <> 0 Then
Yt(I, 1) = (ScaleY * Y(I, 1) + ShiftY) ^ CurrentTransf(1)
Else
Yt(I, 1) = Log(ScaleY * Y(I, 1) + ShiftY)
End If
If CurrentTransf(2) <> 0 Then
Xt(I, 1) = (ScaleX * X(I, 1) + ShiftX) ^ CurrentTransf(2)
Else
Xt(I, 1) = Log(ScaleX * X(I, 1) + ShiftX)
End If
For J = 1 To PolyOrder
Xtp(I, J) = Xt(I, 1) ^ J
Next J
Next I
' perform the regression calculations
vRegResultsMat = Application.WorksheetFunction.LinEst(Yt, Xtp, True, True)
Rsqr = vRegResultsMat(3, 1)
F = vRegResultsMat(4, 1)
' check if F > F of last result
If F > Cells(MaxResults + 1, Col4) Then
M1 = MaxResults + 1
' write new results to row M
Cells(M1, Col4) = F
Cells(M1, Col4 + 1) = Rsqr
For I = 1 To TN
Cells(M1, Col4 + I + 1) = vRegResultsMat(1, TN - I + 1)
Next I
For I = 1 To NumIndpVars
Cells(M1, Col3 + I - 1) = CurrentTransf(I)
Next I
Range(Cells(2, Col3), Cells(MaxResults + 1, Col5)).Select
Range(Cells(2, Col3), Cells(MaxResults + 1, Col5)).Sort _
    Key1:=Range(Cells(2, Col4), Cells(MaxResults + 1, Col4)), Order1:=xlDescending
End If ' If F > Cells(MaxResults + 1, Col3)
GoTo Here
HandleErr:
ErrorCounter = ErrorCounter + 1
If ErrorCounter > MaxErrors Then
If MsgBox("Reached maximum error limits of " & ErrorCounter & vbCrLf & "Want to stop the process?", vbYesNo + vbQuestion, "Confirmation requested ") = vbYes Then
Exit Sub
Else
sMaxErr = InputBox("Update maximum number of errors", "MaxErrors Input", MaxErrors)
If Trim(sMaxErr) = "" Then
MsgBox "User canceled calculations process", vbOKOnly + vbInformation, "End of Process"
Exit Sub
End If
MaxErrors = CDbl(sMaxErr)
ErrorCounter = 0
End If
End If
Resume Here
Here:
Next iX
Next iY
MsgBox "Done", vbOKOnly + vbInformation, "Success!"
End Sub

Option Explicit
Option Base 1
Function ExFx(ByVal sFx As String, ByRef X() As Variant, _
ByRef Y() As Variant, _
ByVal I As Integer, ByVal NumIVars As Integer) As Double
Dim J As Integer
' replace Xnn starting with the higher indices just in case there
' are more than 9 variables.
sFx = UCase(sFx)
For J = NumIVars To 1 Step -1
sFx = Replace(sFx, "X" & J, "(" & X(I, J) & ")")
Next J
sFx = Replace(sFx, "Y", "(" & Y(I, 1) & ")")
ExFx = Evaluate(sFx)
End Function
Sub BestPoly1()
Const MAX_ERRORS As Double = 1000000# ' initial max error value
Dim ErrorCounter As Double, MaxErrors As Double, sMaxErr As String
Dim NumIndpVars As Integer ' number of independent variables
Dim TN As Integer ' total number of variables = NIV+1
Dim N As Integer ' number of data points
Dim MaxTrans As Integer ' max transformations
Dim MaxResults As Integer ' max results
Dim PolyOrder As Integer
Dim Col1 As Integer, Col2 As Integer, Col3 As Integer
Dim Col4 As Integer, Col5 As Integer
Dim I As Integer, J As Integer, K As Integer
Dim M1 As Integer, M As Integer
Dim VarIdx As Integer, Low As Integer, Hi As Integer
Dim TransfMat() As String, sFx As String
Dim CurrentTransf() As String, CountTransf() As Integer
Dim NumTransf() As Integer ' number of transformations
Dim Y() As Variant, X() As Variant
Dim Yt() As Variant, Xt() As Variant, Xtp() As Variant
Dim iY As Integer, iX As Integer
Dim vRegResultsMat As Variant, WS As Worksheet, newws As Worksheet
Dim F As Double, Rsqr As Double, xval As Double
Dim fMaxCount As Double, fCount As Double, fMilestone As Double
Dim dt1 As Date, dt2 As Date, mresults As Variant
Dim ShiftX As Double, ShiftY As Double, ScaleX As Double, ScaleY As Double
Dim yrng As Range, xrng As Range, xcount As Long, a As Long
Dim lastc As Long, Ty As Range, Tx As Range, xpaste As Long
Dim porder As Long
Set yrng = Application.InputBox(prompt:="Select Y Range", Type:=8)
Set xrng = Application.InputBox(prompt:="Select X Range", Type:=8)
porder = InputBox("Input Poly Order")
Set WS = Workbooks("Multiple Linear Regression using Transformations.xlam").Sheets("Variable Transformations")
Set Ty = WS.Range(WS.Range("A2"), WS.Range("A2").End(xlDown))
Set Tx = WS.Range(WS.Range("B2"), WS.Range("B2").End(xlDown))
xcount = xrng.Columns.Count
mresults = InputBox("Maximum Results to Display")
On Error Resume Next
Set WS = Sheets("Poly1")
If Err.Number <> 0 Then
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Poly1"
Else
End If
Sheets("Poly1").Activate
    With Sheets("Poly1")
        .Cells.ClearContents
        .Cells(1, 1).Value = "Polynomial Order"
        .Cells(2, 1).Value = porder
        .Cells(3, 1).Value = "Max Results"
        .Cells(4, 1).Value = mresults
        .Cells(5, 1).Value = "Shift X"
        .Cells(6, 1).Value = InputBox("Shift X Value")
        .Cells(7, 1).Value = "Scale X"
        .Cells(8, 1).Value = InputBox("Scale X Value")
        .Cells(9, 1).Value = "Shift Y"
        .Cells(10, 1).Value = InputBox("Shift Y Value")
        .Cells(11, 1).Value = "Scale Y"
        .Cells(12, 1).Value = InputBox("Scale Y Value")
        .Cells(1, 2).Value = "Y"
        .Cells(2, 2).Resize(yrng.Rows.Count, yrng.Columns.Count).Cells.Value = _
            yrng.Cells.Value
            For a = 1 To xcount
                Cells(1, a + 2).Value = "X" & a
            Next a
        .Cells(2, 3).Resize(xrng.Rows.Count, xrng.Columns.Count).Cells.Value = _
            xrng.Cells.Value
    End With
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(1, lastc + 2).Value = "Transform Y"
Cells(2, lastc + 2).Resize(Ty.Rows.Count, Ty.Columns.Count).Cells.Value = _
            Ty.Cells.Value
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
    For a = 1 To xcount
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
        Cells(1, lastc + 1).Value = "Transform " & "X" & a
    Next a
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(2, lastc - xcount + 1).Resize(Tx.Rows.Count, Tx.Columns.Count).Cells.Value = _
    Tx.Cells.Value
xpaste = 1
    For a = xpaste To xcount - 1
        Range(Cells(2, lastc - xcount + a), Cells(Rows.Count, lastc - xcount + a).End(xlUp)).Select
        Selection.Copy Selection.Offset(0, 1)
        Selection.Offset(0, 1).Select
        Selection.Replace "X" & a, "X" & a + 1, xlPart
    Next a
dt1 = Now
ErrorCounter = 0
MaxErrors = MAX_ERRORS
NumIndpVars = 2
PolyOrder = [A2].Value
MaxResults = [A4].Value
TN = PolyOrder + 1
ShiftX = [A6].Value
ScaleX = [A8].Value
ShiftY = [A10].Value
ScaleY = [A12].Value
Col1 = 2 ' first column of data
Col2 = 5 ' first column of transformations
Col3 = 8 ' first column of results
Col4 = 10 ' first column of tranformatioons
Col5 = Col4 + TN + 1 ' last column of transformations
Range(Cells(1 + Col3), Cells(1, 50)).Value = ""
Cells(1, Col3) = "Transform Y"
Cells(1, Col3 + 1) = "Transform X"
Cells(1, Col4) = "F"
Cells(1, Col4 + 1) = "Rsqr"
    For I = 0 To PolyOrder
        Cells(1, Col4 + 2 + I) = "A" & I
    Next I
Range(Cells(2, Col3), Cells(1 + 2 * MaxResults, Col4 + 3 * TN)).Value = ""
Range(Cells(2, Col4), Cells(1 + MaxResults, Col4 + 1 * TN)).Value = 0
MaxTrans = Range(Cells(2, Col2), Cells(1, Col2)).CurrentRegion.Rows.Count - 1
ReDim NumTransf(NumIndpVars), TransfMat(NumIndpVars, MaxTrans), CurrentTransf(NumIndpVars)
ReDim CountTransf(NumIndpVars)
fMaxCount = 1
For I = 1 To NumIndpVars
    J = 2
    Do While Trim(Cells(J, Col2 + I - 1)) <> ""
        TransfMat(I, J - 1) = Cells(J, Col2 + I - 1)
        J = J + 1
    Loop
NumTransf(I) = J - 2
fMaxCount = fMaxCount * NumTransf(I)
Next I
N = Range("B1").CurrentRegion.Rows.Count - 1
Y = Range("B2:B" & N + 1).Value
X = Range(Cells(2, 3), Cells(N + 1, 2 + PolyOrder)).Value
Yt = Range("B2:B" & N + 1).Value
Xt = Range(Cells(2, 3), Cells(N + 1, 2 + PolyOrder)).Value
ReDim Xtp(N, PolyOrder) 'polynomial data matrix
For iY = 1 To NumTransf(1)
    CurrentTransf(1) = TransfMat(1, iY)
For iX = 1 To NumTransf(2)
    CurrentTransf(2) = TransfMat(2, iX)
fCount = 0
fMilestone = 0.1
Do
On Error GoTo HandleErr
    For I = 1 To N
        DoEvents
    If fCount / fMaxCount > fMilestone Then
        DoEvents
    Application.StatusBar = "Processed " & CStr(fMilestone * 100) & " %"
    If fMilestone < 1 Then fMilestone = fMilestone + 0.05
    End If
If CurrentTransf(1) <> 0 Then
    Yt(I, 1) = (ScaleY * Y(I, 1) + ShiftY) ^ CurrentTransf(1)
Else
    Yt(I, 1) = Log(ScaleY * Y(I, 1) + ShiftY)
End If
    
If CurrentTransf(2) <> 0 Then
    Xt(I, 1) = (ScaleX * X(I, 1) + ShiftX) ^ CurrentTransf(2)
Else
    Xt(I, 1) = Log(ScaleX * X(I, 1) + ShiftX)
End If
    
For J = 1 To PolyOrder
    Xtp(I, J) = ExFx(CurrentTransf(I + 1), X, Y, I, NumIndpVars) ^ J
Next J
Next I
' perform the regression calculations
vRegResultsMat = Application.WorksheetFunction.LinEst(Yt, Xtp, True, True)
Rsqr = vRegResultsMat(3, 1)
F = vRegResultsMat(4, 1)
' check if F > F of last result
If F > Cells(MaxResults + 1, Col4) Then
xval = fCount / fMaxCount * 100
xval = CInt(100 * xval) / 100
Application.StatusBar = "Processed " & CStr(xval) & " %"
M1 = MaxResults + 1
' write new results to row M
Cells(M1, Col4) = F
Cells(M1, Col4 + 1) = Rsqr
For I = 1 To TN
    Cells(M1, Col4 + I + 1) = vRegResultsMat(1, TN - I + 1)
Next I
For I = 1 To NumIndpVars
    Cells(M1, Col3 + I - 1) = CurrentTransf(I)
Next I
Range(Cells(2, Col3), Cells(MaxResults + 1, Col5)).Select
Range(Cells(2, Col3), Cells(MaxResults + 1, Col5)).Sort Key1:=Range(Cells(2, Col4), Cells(MaxResults + 1, Col4)), Order1:=xlDescending
End If ' If F > Cells(MaxResults + 1, Col3)
GoTo Here
HandleErr:
fCount = fCount - 1
ErrorCounter = ErrorCounter + 1
If ErrorCounter > MaxErrors Then
    If MsgBox("Reached maximum error limits of " & ErrorCounter & vbCrLf & _
"Want to stop the process?", vbYesNo + vbQuestion, "Confirmation requested") = vbYes Then
    Exit Sub
Else
    sMaxErr = InputBox("Update maximum number of errors", "Max Errors Input", MaxErrors)
If Trim(sMaxErr) = "" Then
    MsgBox "User canceled calculations process", vbOKOnly + vbInformation, "End of Process"
    Exit Sub
End If
MaxErrors = CDbl(sMaxErr)
ErrorCounter = 0
End If
End If
Resume Here
Here:
    Next iX
Next iY
' ---------------------------------------------------------
' ---------------------------------------------------------
' ------------ Simulate Nested Loops ---------------------
' ---------------------------------------------------------
' ---------------------------------------------------------
For VarIdx = 1 To TN
    
If CountTransf(VarIdx) >= NumTransf(VarIdx) Then
    If VarIdx < TN Then
        CurrentTransf(VarIdx) = TransfMat(VarIdx, 1)
        CountTransf(VarIdx) = 1
    Else
        
    End If
Else
    CountTransf(VarIdx) = CountTransf(VarIdx) + 1
    CurrentTransf(VarIdx) = TransfMat(VarIdx, CountTransf(VarIdx))
    fCount = fCount + 1
    Exit For
End If
Next VarIdx
Loop
On Error GoTo 0
dt2 = Now
[A6].Value = "Start"
[A7].Value = dt1
[A8].Value = "End"
[A9].Value = dt2
Range(Cells(1, 1), Cells(Rows.Count, lastc).End(xlUp)).EntireColumn.AutoFit
Range(Cells(1, 1), Cells(Rows.Count, lastc).End(xlUp)).HorizontalAlignment = xlCenter
MsgBox "Start at " & CStr(dt1) & vbCrLf & _
"End at " & CStr(dt2), vbOKOnly + vbInformation, "Success!"
End Sub

 

[michaelsmith559]

Here is what the "originalbestpolyreg" does (notice the transforms are number coded):

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Polynomial Order	Y	X		Trnsf Y	Trnsf X1		Transf Y	Transf X	F	Rsqr	A0	A1	A2	A3
2	3	2	1		-4	-4		-4	-4	736143.2	0.999842	2.34E-06	-0.15453	94.62684	-94.4098
3	Max Results	3	3		-3	-3		1	1	513238.8	0.999774	1.124051	0.996421	3.04E-06	-7.5E-10
4	25	6	4		-2	-2		-4	-3	439170.3	0.999736	3.02E-06	-0.13202	13.00285	-12.8083
5	Shift X	6	9		-1	-1		2	2	436141	0.999734	1405.325	0.995648	2.01E-09	-2.2E-16
6	0	16	6		0	0		2	1	435018.9	0.999733	1721.827	-8.61012	1.009335	-2.6E-06
7	Scale X	34	26		1	1		3	3	296808.7	0.999609	4349973	0.992312	1.44E-12	-6.1E-23
8	1	36	52		2	2		3	1	294133.6	0.999606	6798093	-37826.7	41.66463	0.988221
9	Shift Y	37	38		3	3		3	2	276389.6	0.99958	-6.1E+07	740.0675	0.000378	-1.5E-11
10	0	46	38		4	4		4	4	199338.7	0.999418	1.44E+10	0.987465	9.03E-16	-1.4E-29
11	Scale Y	46	55					4	2	196555.3	0.99941	1.61E+10	-58833.5	1.028637	-3.3E-09
12	1	47	46					4	3	185261.1	0.999374	-1.3E+11	1163.247	1.23E-07	-2.3E-18
Sheet1

Here is what "bestmlr2" does (notice the expressions):

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Number of X Variables	Y	X1		Transform Y	Transform X1		F	Rsq	Intercept	Slope1	Transform Y	TransformX1
2	1	2	1		Y	X1		1547630.174	0.999773899	0.425605861	0.999682506	Y	X1
3	Max Results	3	3		1/Y	X1^2		1547630.174	0.999773899	0.21280293	0.499841253	Y/2	X1
4	25	6	4		1/Y^2	1/X1		1547630.174	0.999773899	0.425605861	0.999682506	Y	X1*(2*X2+1)
5		6	9		Y^2	1/X1^2		1547630.174	0.999773899	0.21280293	0.499841253	Y/2	X1*(2*X2+1)
6	Start	16	6		LN(Y)	1/SQRT(X1)		1547630.174	0.999773899	0.425605861	1.999365012	Y	X1/2
7	3/16/2017 13:37	34	26		1/SQRT(Y)	SQRT(X1)		1547630.174	0.999773899	0.21280293	0.999682506	Y/2	X1/2
8	End	36	52		SQRT(Y)	LN(X1)		1547630.174	0.999773899	0.425605861	2.999047519	Y	X1/3
9	3/16/2017 13:38	37	38		Y^3	LN(2*X1+1)		1547630.174	0.999773899	0.21280293	1.499523759	Y/2	X1/3
10		46	38		1/3^Y	X1*(2*X2+1)		1547630.174	0.999773899	0.14186862	0.999682506	Y/3	X1/3
11		46	55		Y/2	X1^3		1547630.174	0.999773899	0.07093431	0.499841253	Y/6	X1/3
12		47	46		Y/3	1/3^X1		1547630.174	0.999773899	0.425605861	5.998095037	Y	X1/6
13		48	48		Y/6	EXP(X1)		1547630.174	0.999773899	0.21280293	2.999047519	Y/2	X1/6
14		64	49		Y^1.99	X1/2		1547630.174	0.999773899	0.14186862	1.999365012	Y/3	X1/6
15		71	80			X1/3		1547630.174	0.999773899	0.07093431	0.999682506	Y/6	X1/6
16		78	78			X1/6		1547630.174	0.999773899	0.14186862	0.333227502	Y/3	X1
17		78	85			LN(X1)^2		1547630.174	0.999773899	0.07093431	0.166613751	Y/6	X1
18		85	78					1547630.174	0.999773899	0.14186862	0.333227502	Y/3	X1*(2*X2+1)
19		94	92					1547630.174	0.999773899	0.07093431	0.166613751	Y/6	X1*(2*X2+1)
20		124	103					1547630.174	0.999773899	0.14186862	0.666455004	Y/3	X1/2
MLR1

I am trying to make the polynomial code be able to use expressions like the bestmlr2 code.

 

[shg]

The Do after the For iX = ... is not closed (there is no Loop statement).

 

[michaelsmith559]

Here is the results of the macro so far. It does not seem to pass the expressions at some point. Here is what it does so far, I moved the Next iX and Next iY to the HandleErr (not sure if this is correct).

Screenshot:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Polynomial Order	Y	X1		Transform Y	Transform X1		Transform Y	Transform X	F	Rsqr	A0	A1	A2	A3
2	3	2	1		Y	X1				0	0	0	0	0	0
3	Max Results	3	3		1/Y	X1^2				0	0	0	0	0	0
4	25	6	4		1/Y^2	1/X1				0	0	0	0	0	0
5	Shift X	6	9		Y^2	1/X1^2				0	0	0	0	0	0
6	0	16	6		LN(Y)	1/SQRT(X1)				0	0	0	0	0	0
7	Scale X	34	26		1/SQRT(Y)	SQRT(X1)				0	0	0	0	0	0
8	1	36	52		SQRT(Y)	LN(X1)				0	0	0	0	0	0
9	Shift Y	37	38		Y^3	LN(2*X1+1)				0	0	0	0	0	0
10	0	46	38		1/3^Y	X1*(2*X2+1)				0	0	0	0	0	0
11	Scale Y	46	55		Y/2	X1^3				0	0	0	0	0	0
12	1	47	46		Y/3	1/3^X1				0	0	0	0	0	0
13		48	48		Y/6	EXP(X1)				0	0	0	0	0	0
14		64	49		Y^1.99	X1/2				0	0	0	0	0	0
15		71	80			X1/3				0	0	0	0	0	0
16		78	78			X1/6				0	0	0	0	0	0
17		78	85			LN(X1)^2				0	0	0	0	0	0
18		85	78							0	0	0	0	0	0
19		94	92							0	0	0	0	0	0
20		124	103							0	0	0	0	0	0
Poly1

I think this part of the code has to be changed to reflect what is in the bestmlr2:

If CurrentTransf(1) <> 0 Then
    Yt(I, 1) = (ScaleY * Y(I, 1) + ShiftY) ^ CurrentTransf(1)
Else
    Yt(I, 1) = Log(ScaleY * Y(I, 1) + ShiftY)
End If

If CurrentTransf(2) <> 0 Then
    Xt(I, 1) = (ScaleX * X(I, 1) + ShiftX) ^ CurrentTransf(2)
Else
    Xt(I, 1) = Log(ScaleX * X(I, 1) + ShiftX)
End If

    For J = 1 To PolyOrder
        Xtp(I, J) = Xt(I, 1) ^ J
    Next J

Next I

MLR2 Code:

' sFx = CurrentTransf(1) '[A5].Value
Yt(I, 1) = ExFx(CurrentTransf(1), X, Y, I, NumIndpVars)
For J = 1 To NumIndpVars
'sFx = CurrentTransf(J + 1) ' Range("A" & M).Value
Xt(I, J) = ExFx(CurrentTransf(J + 1), X, Y, I, NumIndpVars)
Next J
Next I

 

[michaelsmith559]

Here is how I have changed the code so far:

Option Explicit
Option Base 1
Function ExFx(ByVal sFx As String, ByRef X() As Variant, _
ByRef Y() As Variant, _
ByVal I As Integer, ByVal NumIVars As Integer) As Double
Dim J As Integer
' replace Xnn starting with the higher indices just in case there
' are more than 9 variables.
sFx = UCase(sFx)
For J = NumIVars To 1 Step -1
sFx = Replace(sFx, "X" & J, "(" & X(I, J) & ")")
Next J
sFx = Replace(sFx, "Y", "(" & Y(I, 1) & ")")
ExFx = Evaluate(sFx)
End Function
Sub BestPoly()
Const MAX_ERRORS As Double = 1000000# ' initial max error value
Dim ErrorCounter As Double, MaxErrors As Double, sMaxErr As String
Dim NumIndpVars As Integer ' number of independent variables
Dim TN As Integer ' total number of variables = NIV+1
Dim N As Integer ' number of data points
Dim MaxTrans As Integer ' max transformations
Dim MaxResults As Integer ' max results
Dim Col1 As Integer, Col2 As Integer, Col3 As Integer
Dim Col4 As Integer, Col5 As Integer
Dim I As Integer, J As Integer, K As Integer
Dim M1 As Integer, M As Integer
Dim VarIdx As Integer, Low As Integer, Hi As Integer
Dim TransfMat() As String, sFx As String
Dim CurrentTransf() As String, CountTransf() As Integer
Dim NumTransf() As Integer ' number of transformations
Dim Y() As Variant, X() As Variant, iY As Integer, iX As Integer
Dim Yt() As Variant, Xt() As Variant, Xtp() As Variant
Dim vRegResultsMat As Variant, WS As Worksheet, newws As Worksheet
Dim F As Double, Rsqr As Double, xval As Double
Dim fMaxCount As Double, fCount As Double, fMilestone As Double
Dim dt1 As Date, dt2 As Date, mresults As Variant
Dim ShiftX As Double, ShiftY As Double, ScaleX As Double, ScaleY As Double

Dim yrng As Range, xrng As Range, xcount As Long, a As Long
Dim lastc As Long, Ty As Range, Tx As Range, xpaste As Long
Dim porder As Long, PolyOrder As Long

Set yrng = Application.InputBox(prompt:="Select Y Range", Type:=8)
Set xrng = Application.InputBox(prompt:="Select X Range", Type:=8)
porder = InputBox("Input Poly Order")
Set WS = Workbooks("MLR using Transformations.xlam").Sheets("Transforms")
Set Ty = WS.Range(WS.Range("A2"), WS.Range("A2").End(xlDown))
Set Tx = WS.Range(WS.Range("B2"), WS.Range("B2").End(xlDown))

xcount = xrng.Columns.Count

mresults = InputBox("Maximum Results to Display")

On Error Resume Next
Set WS = Sheets("Poly1")
If Err.Number <> 0 Then
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Poly1"
Else
End If
Sheets("Poly1").Activate
    With Sheets("Poly1")
        .Cells.ClearContents
        .Cells(1, 1).Value = "Polynomial Order"
        .Cells(2, 1).Value = porder
        .Cells(3, 1).Value = "Max Results"
        .Cells(4, 1).Value = mresults
        .Cells(5, 1).Value = "Shift X"
        .Cells(6, 1).Value = InputBox("Shift X Value")
        .Cells(7, 1).Value = "Scale X"
        .Cells(8, 1).Value = InputBox("Scale X Value")
        .Cells(9, 1).Value = "Shift Y"
        .Cells(10, 1).Value = InputBox("Shift Y Value")
        .Cells(11, 1).Value = "Scale Y"
        .Cells(12, 1).Value = InputBox("Scale Y Value")
        .Cells(1, 2).Value = "Y"
        .Cells(2, 2).Resize(yrng.Rows.Count, yrng.Columns.Count).Cells.Value = _
            yrng.Cells.Value
            For a = 1 To xcount
                Cells(1, a + 2).Value = "X" & a
            Next a
        .Cells(2, 3).Resize(xrng.Rows.Count, xrng.Columns.Count).Cells.Value = _
            xrng.Cells.Value
    End With
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(1, lastc + 2).Value = "Transform Y"
Cells(2, lastc + 2).Resize(Ty.Rows.Count, Ty.Columns.Count).Cells.Value = _
            Ty.Cells.Value
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
    For a = 1 To xcount
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
        Cells(1, lastc + 1).Value = "Transform " & "X" & a
    Next a
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(2, lastc - xcount + 1).Resize(Tx.Rows.Count, Tx.Columns.Count).Cells.Value = _
    Tx.Cells.Value
xpaste = 1
    For a = xpaste To xcount - 1
        Range(Cells(2, lastc - xcount + a), Cells(Rows.Count, lastc - xcount + a).End(xlUp)).Select
        Selection.Copy Selection.Offset(0, 1)
        Selection.Offset(0, 1).Select
        Selection.Replace "X" & a, "X" & a + 1, xlPart
    Next a

dt1 = Now
ErrorCounter = 0
MaxErrors = MAX_ERRORS
NumIndpVars = 2
PolyOrder = [A2].Value
MaxResults = [A4].Value
TN = PolyOrder + 1
ShiftX = [A6].Value
ScaleX = [A8].Value
ShiftY = [A10].Value
ScaleY = [A12].Value
Col1 = 2 ' first column of data
Col2 = 5 ' first column of transformations
Col3 = 8 ' first column of results
Col4 = 10 ' first column of tranformatioons
Col5 = Col4 + TN + 1 ' last column of transformations

Range(Cells(1 + Col3), Cells(1, 50)).Value = ""
Cells(1, Col3) = "Transform Y"
Cells(1, Col3 + 1) = "Transform X"
Cells(1, Col4) = "F"
Cells(1, Col4 + 1) = "Rsqr"
    For I = 0 To PolyOrder
        Cells(1, Col4 + 2 + I) = "A" & I
    Next I

Range(Cells(2, Col3), Cells(1 + 2 * MaxResults, Col4 + 3 * TN)).Value = ""
Range(Cells(2, Col4), Cells(1 + MaxResults, Col4 + 1 + TN)).Value = 0
MaxTrans = Range(Cells(2, Col2), Cells(1, Col2)).CurrentRegion.Rows.Count - 1

ReDim NumTransf(NumIndpVars), TransfMat(NumIndpVars, MaxTrans), CurrentTransf(NumIndpVars)
ReDim CountTransf(NumIndpVars)

fMaxCount = 1
For I = 1 To NumIndpVars
    J = 2
    Do While Trim(Cells(J, Col2 + I - 1)) <> ""
        TransfMat(I, J - 1) = Cells(J, Col2 + I - 1)
        J = J + 1
    Loop
NumTransf(I) = J - 2
fMaxCount = fMaxCount * NumTransf(I)
Next I

N = Range("B1").CurrentRegion.Rows.Count - 1
Y = Range("B2:B" & N + 1).Value
X = Range(Cells(2, 3), Cells(N + 1, 2 + PolyOrder)).Value
Yt = Range("B2:B" & N + 1).Value
Xt = Range(Cells(2, 3), Cells(N + 1, 2 + PolyOrder)).Value

ReDim Xtp(N, PolyOrder) 'polynomial data matrix

For iY = 1 To NumTransf(1)
    CurrentTransf(1) = TransfMat(1, iY)
For iX = 1 To NumTransf(2)
    CurrentTransf(2) = TransfMat(2, iX)
    CountTransf(I) = 1

fCount = 0
fMilestone = 0.1
Do

On Error GoTo HandleErr

For I = 1 To N
    DoEvents
If fCount / fMaxCount > fMilestone Then
    DoEvents
    Application.StatusBar = "Processed " & CStr(fMilestone * 100) & " %"
If fMilestone < 1 Then fMilestone = fMilestone + 0.05
End If

If CurrentTransf(1) <> 0 Then
    Yt(I, 1) = (ScaleY * Y(I, 1) + ShiftY) ^ CurrentTransf(1)
Else
    Yt(I, 1) = Log(ScaleY * Y(I, 1) + ShiftY)
End If

If CurrentTransf(2) <> 0 Then
    Xt(I, 1) = (ScaleX * X(I, 1) + ShiftX) ^ CurrentTransf(2)
Else
    Xt(I, 1) = Log(ScaleX * X(I, 1) + ShiftX)
End If

    For J = 1 To PolyOrder
        Xtp(I, J) = Xt(I, 1) ^ J
    Next J

Next I

' perform the regression calculations
vRegResultsMat = Application.WorksheetFunction.LinEst(Yt, Xtp, True, True)
Rsqr = vRegResultsMat(3, 1)
F = vRegResultsMat(4, 1)
' check if F > F of last result
If F > Cells(MaxResults + 1, Col4) Then
xval = fCount / fMaxCount * 100
xval = CInt(100 * xval) / 100
Application.StatusBar = "Processed " & CStr(xval) & " %"
M1 = MaxResults + 1
' write new results to row M
Cells(M1, Col4) = F
Cells(M1, Col4 + 1) = Rsqr
For I = 1 To TN
    Cells(M1, Col4 + I + 1) = vRegResultsMat(1, TN - I + 1)
Next I

For I = 1 To NumIndpVars
    Cells(M1, Col3 + I - 1) = CurrentTransf(I)
Next I

Range(Cells(2, Col3), Cells(MaxResults + 1, Col5)).Select
Range(Cells(2, Col3), Cells(MaxResults + 1, Col5)).Sort Key1:=Range(Cells(2, Col4), Cells(MaxResults + 1, Col4)), Order1:=xlDescending

End If ' If F > Cells(MaxResults + 1, Col3)

    GoTo Here

HandleErr:
    fCount = fCount - 1
    ErrorCounter = ErrorCounter + 1
If ErrorCounter > MaxErrors Then
    If MsgBox("Reached maximum error limits of " & ErrorCounter & vbCrLf & _
        "Want to stop the process?", vbYesNo + vbQuestion, "Confirmation requested") = vbYes Then
    Exit Sub
Else
    sMaxErr = InputBox("Update maximum number of errors", "Max Errors Input", MaxErrors)
If Trim(sMaxErr) = "" Then
    MsgBox "User canceled calculations process", vbOKOnly + vbInformation, "End of Process"
    Exit Sub
End If
    MaxErrors = CDbl(sMaxErr)
    ErrorCounter = 0
    End If
End If
Resume Here
Here:

' ---------------------------------------------------------
' ---------------------------------------------------------
' ------------ Simulate Nested Loops ---------------------
' ---------------------------------------------------------
' ---------------------------------------------------------
For VarIdx = 1 To TN
DoEvents
If CountTransf(VarIdx) >= NumTransf(VarIdx) Then
If VarIdx < TN Then
CurrentTransf(VarIdx) = TransfMat(VarIdx, 1)
CountTransf(VarIdx) = 1
Else
Exit Do
End If
Else
CountTransf(VarIdx) = CountTransf(VarIdx) + 1
CurrentTransf(VarIdx) = TransfMat(VarIdx, CountTransf(VarIdx))
fCount = fCount + 1
Exit For
End If
Next VarIdx
Loop

    Next iX
Next iY

On Error GoTo 0
dt2 = Now
[A6].Value = "Start"
[A7].Value = dt1
[A8].Value = "End"
[A9].Value = dt2
Range(Cells(1, 1), Cells(Rows.Count, lastc).End(xlUp)).EntireColumn.AutoFit
Range(Cells(1, 1), Cells(Rows.Count, lastc).End(xlUp)).HorizontalAlignment = xlCenter

Application.StatusBar = "Done"

MsgBox "Start at " & CStr(dt1) & vbCrLf & _
"End at " & CStr(dt2), vbOKOnly + vbInformation, "Success!"

End Sub

 

[shg]

Broadly, what is all of this supposed to do? A regression based on arbitrary basis functions?

 

[michaelsmith559]

Here is the website:
Namir Shammas Web Pages

On the webpage, scroll to the third entry from the bottom: "Regression Modeling with VBA"

When you click on the link, there will be 3 pdf's to download or one zip file. Anyhow, the polynomial one is the third file, and this macro is for the first scheme. It allows, you to transform the x and y variable to find the best fit. If I get this one like I want I will also apply this to his scheme 2 where you can compare different polynomial regressions. Thanks for taking a look.

Mike

 

[shg]

I glanced through the article. Aside from being a fun exercise, why not just perform whatever transformations you like in a LINEST formula in the UI?

 

[michaelsmith559]

Some of the transformations, I would not know how to do using linest. There are others that I may add over time and I personally would find it much easier to have a list of transformations to use instead of linest. I agree, it is a fun exercise. Just hope I can figure out how to make the polynomial regression use the expressions like the mlr does.

 

[shg]

Some of the transformations, I would not know how to do using linest.

To pick one at random,

=linest(y, ln(2*x+1))