Inconsistent Answers with LINEST

[mst3k4L]

I have two dynamic ranges "kro_Sg" and kro_Data". I am referencing these in the kro function, which uses them to call the BestFitEasy function. The BestFitEasy function then uses the local version of these ranges ("RangeX" and "RangeY") and the LINEST command to find the R2 value of the ranges. It is able to find a value, but the problem is that the value is not the same compared to LINEST using "Kro_Sg" and "kro_Data". The code is below.

Function kro(sg) As Double
    
    Application.Volatile

    kro = BestFitEasy(sg, Sheet2.Range("kro_Sg"), Sheet2.Range("kro_Data"))
    
End Function
Function BestFitEasy(input_value, RangeX As Range, RangeY As Range) As Double

    poly3_r2 = Evaluate("INDEX(LINEST(RangeY,RangeX^{1,2,3},,1),3,1)")
    poly32_r2 = Evaluate("INDEX(LINEST(kro_Data,kro_Sg^{1,2,3},,1),3,1)")

End Function

As you can see, poly3_r2 and poly32_r2 should be the same, but they are different values. poly3_r2 = 0.996 and poly32_r2 = 1.0. This is the entirety of the code, so the values should not be changing.

I also don't know how to display any of these ranges in the intermediate window. That might help with the debugging process.

 

[shg]

Function BestFitEasy(input_value, RangeX As Range, RangeY As Range) As Double
 
    poly3_r2 = Evaluate("INDEX(LINEST(RangeY,RangeX^{1,2,3},,1),3,1)")
    poly32_r2 = Evaluate("INDEX(LINEST(kro_Data,kro_Sg^{1,2,3},,1),3,1)")
 
End Function

In this function, you are receving arguments but not using any of them. RangeY, RangeX, kro_Data, and kro_Sg are in literal strings, which means they are interpreted as named ranges within the scope of the active sheet.

You also aren't assigning any return value to the function.

Maybe this (untested):

Function BestFitEasy(RangeX As Range, RangeY As Range) As Double
    Dim sX As String
    Dim sY As String
 
    sX = RangeX.Address(External:=True)
    sY = RangeY.Address(External:=True)
 
    BestFitEasy = Evaluate("INDEX(LINEST(" & sY & "," & sX & "^{1,2,3},,True),3,1)")
End Function

 

[mst3k4L]

Function BestFitEasy(input_value, RangeX As Range, RangeY As Range) As Double
 
    poly3_r2 = Evaluate("INDEX(LINEST(RangeY,RangeX^{1,2,3},,1),3,1)")
    poly32_r2 = Evaluate("INDEX(LINEST(kro_Data,kro_Sg^{1,2,3},,1),3,1)")
 
End Function

In this function, you are receving arguments but not using any of them. RangeY, RangeX, kro_Data, and kro_Sg are in literal strings, which means they are interpreted as named ranges within the scope of the active sheet.

You also aren't assigning any return value to the function.

Maybe this (untested):

Function BestFitEasy(RangeX As Range, RangeY As Range) As Double
    Dim sX As String
    Dim sY As String
 
    sX = RangeX.Address(External:=True)
    sY = RangeY.Address(External:=True)
 
    BestFitEasy = Evaluate("INDEX(LINEST(" & sY & "," & sX & "^{1,2,3},,True),3,1)")
End Function

Wow! That worked, although I do not understand the problem and don't understand what you mean. When you said I was not using the arguments, did you mean that I was not actually using the values of the arguments? Was that the purpose of defining sX = RangeX.Address? Is there a better way to pass a range so that it can use the values directly without having to define another variable?

Anyway, thanks shg!

 

[shg]

When you said I was not using the arguments, did you mean that I was not actually using the values of the arguments?

Sub x
    y "Hi, I'm Bob"
End Sub
 
Sub y(sInput As String)
    MsgBox "sInput" ' this doesn't use the argument
    MsgBox sInput   ' this does
End Sub