Inconsistent Answers with LINEST

mst3k4L

Board Regular
Joined
Nov 3, 2011
Messages
55
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.

Code:
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.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Code:
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):

Code:
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

Board Regular
Joined
Nov 3, 2011
Messages
55
Code:
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):

Code:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
When you said I was not using the arguments, did you mean that I was not actually using the values of the arguments?
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top