Autoselect Range/Row reference for Linest Array Function

[FellowExcellor]

Hi,

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.

Hope someone can help.

Thanks,

FellowExcellor

 

[charijit]

X=450 falls under the category of "interpolation" and not extrapolation. What I wish to do is say my X=1600 for which I want to find the value of Y. First I'll find the nearest value in the table given above which X=1500. Now since Y=mX+C, I'll take two equations

1400m+C=19.2
1500m+C=18.1

( m = (19.2-18.1)/(1400-1500) ; C = 19.2-1400m)

to solve for m,C. Thereafter I'll get a straight line equation valid considering the nearest two points to X=1600. Then I can put the value of X=1600 and find the corresponding Y.
The place where I am stuck up is how to compare and pick two adjacent values.

 

[Domenic]

X=450 falls under the category of "interpolation" and not extrapolation. What I wish to do is say my X=1600 for which I want to find the value of Y. First I'll find the nearest value in the table given above which X=1500. Now since Y=mX+C, I'll take two equations

1400m+C=19.2
1500m+C=18.1

( m = (19.2-18.1)/(1400-1500) ; C = 19.2-1400m)

to solve for m,C. Thereafter I'll get a straight line equation valid considering the nearest two points to X=1600. Then I can put the value of X=1600 and find the corresponding Y.
The place where I am stuck up is how to compare and pick two adjacent values.

Since I'm not a statistician, it's probably best that I defer this question to others who have the expertise. The problem, though, your question is buried in this thread and most people will probably overlook it. If you don't receive a response from anyone here, you may want to try and start a new thread so that more people will see it.

 

[charijit]

Domenic,
I thank you for your patience. Neither am I a statistician. All I am trying to is simple arithmetic. Let me explain the problem in a different way:

Let A1=1, A2=2, A3=3, B1=500, B2=506, B3=518. Let's call Column-A values as 'x' and Column-B values as 'y'. The difference between interpolation or extrapolation and regression is a bit different. Say, I want to know the value of 'y' for x = 2.5. This is a case of interpolation because I want to determine the value of 'y' for an 'x' which is within the range of 'x' (a value which is falling within 1-3 range). If I want to know 'y' for x=-0.5 or 3.6, it'll be a case of extrapolation.
What does regression do? It simply determines a equation that best fits the given set of values, based of minimizing the deviations. This enables anyone to find out values outside and inside the range. What I am trying to do is a bit different.
Lets say I am trying to find out 'y' for a value outside the given range of 'x'. If I have to find out 'y' for x=-0.5. I first compare the given values of X to find the nearest value of 'x', which'll be 1. I then for an equation based on A1=1,A2=2,B1=500 & B2=506. And in that equation if I put the value of -0.5 (x) I'll be getting 'y'. I hope I am able to make things clear. My only difficulty is how to find out the nearest value of 'x'!

 

[Domenic]

Let's see if I understand you correctly...

1) If X equals -0.5, the formula should return 500

2) If X equals 1.95, the formula should return 500

3) If X equals 2, the formula should return 506

4) If X equals 2.25, the formula should return 506

5) If X equals 3.6, the formula should return 518

Is this correct? If so, try...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},B1,LOOKUP(D1,$A$1:$A$3,$B$1:$B$3)))

...where D1 contains X, such as -0.5.

Hope this helps!

 

[charijit]

Many thanks, Domenic. It isn't what I want to do, but you have shown me a way out. I need just a slight modification in the formula:

points 1 & 5 results are perfect. However, I want the 2,3 & 4 results to be equal to "interpolation".

 

[Domenic]

Many thanks, Domenic. It isn't what I want to do, but you have shown me a way out. I need just a slight modification in the formula:

points 1 & 5 results are perfect. However, I want the 2,3 & 4 results to be equal to "interpolation".

What did you end up with? Based on your last set of sample data...

Let A1=1, A2=2, A3=3, B1=500, B2=506, B3=518

...I was going to offer the following formula...

=IF(D1<A1,B1,IF(D1>A3,B3,IF(LOOKUP(D1,A1:A3)<>D1,LOOKUP(D1,A1:A3,B1:B3)+((D1-LOOKUP(D1,A1:A3))/(INDEX(A1:A3,MATCH(D1,A1:A3,1)+(LOOKUP(D1,A1:A3)<>D1))-LOOKUP(D1,A1:A3))*(INDEX(B1:B3,MATCH(D1,A1:A3,1)+(LOOKUP(D1,A1:A3)<>D1))-LOOKUP(D1,A1:A3,B1:B3))),LOOKUP(D1,A1:A3,B1:B3))))

 

[charijit]

What did you end up with? Based on your last set of sample data...



...I was going to offer the following formula...

=IF(D1<A1,B1,IF(D1>A3,B3,IF(LOOKUP(D1,A1:A3)<>D1,LOOKUP(D1,A1:A3,B1:B3)+((D1-LOOKUP(D1,A1:A3))/(INDEX(A1:A3,MATCH(D1,A1:A3,1)+(LOOKUP(D1,A1:A3)<>D1))-LOOKUP(D1,A1:A3))*(INDEX(B1:B3,MATCH(D1,A1:A3,1)+(LOOKUP(D1,A1:A3)<>D1))-LOOKUP(D1,A1:A3,B1:B3))),LOOKUP(D1,A1:A3,B1:B3))))

Boy, that's a BIG formulae! If one doesn't use LINEST or TREND, one ends up with such a huge one. I am still writing my formula and would let you know once I finish. Meanwhile, I'll try yours. I need to understand the logic you have followed. Thank you very much.

 

[Domenic]

Actually, the system cut off part of the formula. Replace...

D1A3

with

D1 < A1,B1,IF(D1 > A3