Autoselect Range/Row reference for Linest Array Function

Try the following...

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Then try...

=LINEST(K8:INDEX(K8:K65536,MATCH(BigNum,K8:K65536)),M8:INDEX(N8:N65536,MATCH(BigNum,K8:K65536)),TRUE,TRUE)

Hope this helps!

Hello Domenic,

I tried what you suggested and inserted the formula below but it's returning #REF! in the array output.

=LINEST(K8:INDEX(K8:K65536,MATCH(BigNum,K8:K65536)),M8:INDEX(M8:N65536,MATCH(BigNum,K8:K65536)),TRUE,TRUE)

Any idea why this may not be working?

Thanks,

Fellow Excellor

Change the reference for the second index formula as follows...

=LINEST(K8:INDEX(K8:K65536,MATCH(BigNum,K8:K65536)),M8:INDEX(N8:N65536,MATCH(BigNum,K8:K65536)),TRUE,TRUE)

Or, if you prefer...

=LINEST(K8:INDEX(K8:K65536,MATCH(BigNum,K8:K65536)),M8:INDEX(M8:N65536,MATCH(BigNum,K8:K65536),2),TRUE,TRUE)

Hope this helps!

Hi Domenic,

Thanks for these. It works fine now. If you don't mind, could you please briefly explain how this works??? In particular the BigNum parameter, what does this defined range represent and how is it being used in the formula?

Thanks,

Fellow Excellor

The number 9.99999999999999E+307, which is defined as BigNum, is the largest number recognized by Excel. When used as a lookup value in a lookup formula where an approximate match is sought, it finds the last numerical value within the specified range. Here it's used as a lookup value in MATCH and returns the position of the last numerical value within K8:K65536. This position/number, in turn, is passed to INDEX.

Now normally INDEX/MATCH returns a value. However, in this case, since it's preceded by a colon and Excel expects a reference, it returns a cell reference instead. So, for example, if K8:K10 contain numbers and the cells below are either empty or contain #DIV/0! as in your data, here's how the following is evaluated...

K8:INDEX(K8:K65536,MATCH(BigNum,K8:K65536))

K8:INDEX(K8:K65536,3)

K8:K10

Hope this helps!

Hello I'm trying to do a similar thing using the same format, I don't have any #DIV/0! in my source data though. I want to make the range dynamic so that if more data is added or the series is replaced with a longer or shorter length, the regression parameters update automaticlly to include the new cells.

Currently I am using the formula bleow but its not working and returning #N/A. My x variable is in column B and Y variables in columns F to I.

Anyone know what the issue is with the formula? And what does the number two denote jst before the second before last bracket?

=LINEST(B2:INDEX(B2:B65536,MATCH(BigNum,B2:B65536)),F2:INDEX(F2:I65536,MATCH(BigNum,F2:I65536),2),TRUE,TRUE)

Thanks,

Elvis

The lookup range for MATCH needs to be a vector/one dimensional array. Try replacing...

MATCH(BigNum,F2:I65536)

with

MATCH(BigNum,B2:B65536)

Alternatively, if you're using Excel 2003, convert the data into a list...

Data > List > Create List

If you're using Excel 2007, convert the date into a table (the option should be available under Data). The ranges will automatically adjust as data is added or removed.

Thanks Domenic and FellowExcellor. I needed someone to throw some light on this topic.

Question-1. By assigning BigNum a value, are we trying to force Excel to search for that value and then match the nearest Max value in that Column?

I am trying to prepare a spreadsheet which addresses 3 calculation methods - linear interpolation, linear extrapolation & regression analysis. The data considered for all these methods is as below - Base Data consisting of columns x & y; linear interpolation calculation is done based on the value of X. I look for this value of X in the base data (x) and find the two points on either side of X. Considering the straight line equation : y=mx+c, I have calculated

slope, m =IF(ISBLANK($D4)=TRUE,"",IF(OR(ISNA(MATCH($D4,$A$4:$A$2500,1))=TRUE,$D4>MAX($A$4:$A$2500)),"extrapolate",((INDEX($A$4:$B$2500,MATCH($D4,$A$4:$A$2500,1),2)-INDEX($A$4:$B$2500,MATCH($D4,$A$4:$A$2500,1)+1,2))/(INDEX($A$4:$B$2500,MATCH($D4,$A$4:$A$2500,1),1)-INDEX($A$4:$B$2500,MATCH($D4,$A$4:$A$2500,1)+1,1)))))

Y-intercept, C =IF(ISBLANK($D4)=TRUE,"",IF(OR(ISNA(MATCH($D4,$A$4:$A$2500,1))=TRUE,$D4>MAX($A$4:$A$2500)),"extrapolate",INDEX($A$4:$B$2500,MATCH($D4,$A$4:$A$2500,1),2)-$E4*INDEX($A$4:$B$2500,MATCH($D4,$A$4:$A$2500,1),1)))

Y = mX+C. In all this I have assumed that data will be only in 2500 cells.

Base Data linear interpolation

x y X Slope, m Y-intercept, C Y

-325 30.3 21 -0.0053 28.6706 28.5594
-200 29.7
-100 29.2
70 28.3
200 27.5
300 27
400 26.4
500 25.9
600 25.3
700 24.8
800 24.1
900 23.5
1000 22.8
1100 22
1200 21.2
1300 20.3
1400 19.2
1500 18.1

Question-2. I want to make the range dynamic. How do I incorporate that?
Question-3. If I now want to modify the spreadsheet for extrapolation purpose only, I need to compare the value with the range and proceed with the calculation only if out of range. So lets say I have X = -326 & X = 1600. First I wish to check whether they are out of the min and max of the range. Once I do that I find the nearest value and then pickup the next lower point (e.g. for -326, I'll first find -325 and then -200 for my calculation) and proceed to calculate Y=mX+C. I am not able to figure out a way to compare?

The reason why I am doing this excersize is because there is a big difference in interpolation/extrapolation and regression. The former can be based on two data points and can always be treated as a straight line. This method however will be improper in a nonlinear case. Regression on the other hand takes the wholistic view-point and is a generalized method.
While posting I am able to see the columns as separate, but when I am trying to post it is all coming together (-325 30.3 21 -0.0053 28.6706 28.5594). The values correspond to (x y X m C Y).
x = independent variable
y = dependent variable
X = new value
m = slope of the straight line consisting of those two points
C = Y-intercept
Y = dependent variable to be found

Question-1. By assigning BigNum a value, are we trying to force Excel to search for that value and then match the nearest Max value in that Column?

Click to expand...

BigNum, which is defined as 9.99999999999999E+307, is the largest number recognized by Excel. When used as the lookup value in lookup function, such as MATCH, LOOKUP, and VLOOKUP, where an approximate match is sought, the last numerical value in the lookup range is returned.

Question-2. I want to make the range dynamic. How do I incorporate that?

Click to expand...

If you're using Excel 2003 or 2007, convert your data into a list or table. The range will automatically adjust as data is added/removed. Otherwise, use a dynamic named range...

Insert > Name > Define

Name: Range

Refers to:

=$A$4:INDEX($A$4:$A$65536,MATCH(BigNum,$A$4:$A$65536,1))

Click Ok

Then, in your formula, replace $A$4:$A$2500 with the defined name Range.

Question-3. If I now want to modify the spreadsheet for extrapolation purpose only, I need to compare the value with the range and proceed with the calculation only if out of range. So lets say I have X = -326 & X = 1600. First I wish to check whether they are out of the min and max of the range. Once I do that I find the nearest value and then pickup the next lower point (e.g. for -326, I'll first find -325 and then -200 for my calculation) and proceed to calculate Y=mX+C. I am not able to figure out a way to compare?

Click to expand...

I'm assuming that if X equals 450, the expected result is 500, correct? What's the expected result if X equals 1600?