LINEST question

**DRFLETT** · 06-19-2008, 06:51 AM

I have a table something like this (much bigger in reality)

1 5
2 10
5 15
7 #N/A
9 20
12 25

I need to interpret the above data with the linest function, but for a variety of reasons i cant delete the error square. How can i get the LINEST to ignore the #N/A and the 7? otherwise it returns an error.

Many thanks in advance

Andy

**mrice** · 06-19-2008, 02:25 PM

How about creating a parallel set of data using linked formulae such as...

=IF(ISNA($B1),"",A1) in D1 and
=IF(ISNA($B1),"",B1) in E1

which will turn #N/A's and their adjacent values into blanks.

**Andy Pope** · 06-19-2008, 03:56 PM

I don't think you can ignore it with formula. You can get it treated as zero but that will disort the result.

You need to use a contiguous range if valid values, which could be built with formula or code.

**Domenic** · 06-19-2008, 04:04 PM

Assuming that A2:B7 contains the data, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=LINEST(N(OFFSET(A2:A7,SMALL(IF(ISNUMBER(B2:B7),ROW(B2:B7)-ROW(B2)),ROW(INDIRECT("1:"&COUNT(B2:B7)))),0,1)),N(OFFSET(B2:B7,SMALL(IF(ISNUMBER(B2:B7),ROW(B2:B7)-ROW(B2)),ROW(INDIRECT("1:"&COUNT(B2:B7)))),0,1)))

Adjust the range and parameters, accordingly.

Hope this helps!

**Andy Pope** · 06-19-2008, 04:14 PM

Hi Domenic,

Very smart!

Can I assume that formula is sorting the values, sending the #N/A's to an end and then adjusting the number of values used.

**Domenic** · 06-19-2008, 09:02 PM

Originally Posted by Andy Pope

Hi Domenic,

Very smart!

Can I assume that formula is sorting the values, sending the #N/A's to an end and then adjusting the number of values used.

Hi Andy,

Actually, the #N/A's are ignored altogether. Based on the sample data, here's how the following is evaluated...

N(OFFSET(A2:A7,SMALL(IF(ISNUMBER(B2:B7),ROW(B2:B7)-ROW(B2)),ROW(INDIRECT("1:"&COUNT(B2:B7)))),0,1))

N(OFFSET(A2:A7,SMALL(IF({TRUE;TRUE;TRUE;FALSE;TRUE;TRUE},{0;1;2;3;4;5}),{1;2;3;4;5}),0,1))

N(OFFSET(A2:A7,SMALL({0;1;2;FALSE;4;5},{1;2;3;4;5}),0,1))

N(OFFSET({A2:A7,{0;1;2;4;5},0,1))

{1;2;5;9;12}

Hope this helps!

**Andy Pope** · 06-20-2008, 04:41 AM

Thanks for the explanation.

I love complex formula but boy are they hard to decipher

**DRFLETT** · 06-20-2008, 05:00 AM

You are right about the "" idea, i tried it and they generate an error too. Also when i plot the results with "" they generate a zero on my graph, that why I generated a #N/A instead!

Thanks for the code. I have no idea how that might work!!

I'm having trouble with it though. I'm getting an error message.

Will it work if two consecutive rows are #N/A?

Here is a sample of my actual data set.

147 -5272
172 -4172
200 -3300
225 -2293
252 -1370
277 #N/A
305 #N/A
330 903
357 1524
382 2192
407 2803
435 3335
460 3893
487 4149
512 4685
540 4887
565 5221
592 5654

**Andy Pope** · 06-20-2008, 05:15 AM

Dominic's formula works for me.

See attached.

**DRFLETT** · 06-20-2008, 05:15 AM

You are some kind of genius. I fiddled with it and eventually it worked.

I have another question however!!

Can i alter your formula to use linest in a polynomial regression analysis using {1,2} on the x values?

**Andy Pope** · 06-20-2008, 05:36 AM

The final 2 arguments of the function can be added as normal, just before the final closing )

**DRFLETT** · 06-20-2008, 06:31 AM

I know i am pushing my luck now, but I am so chuffed that your solution worked!

Is it possible to have the linest function look at the whole column, but only analyse the data in the 5 cells above the #N/A and the 5 cells below?

The reason is that the point i need to interpolate is always in that data set and the points outside tend to make it less accurate.

Thanks so much for your help

Andy

**Andy Pope** · 06-20-2008, 07:56 AM

It may be possible but I think if you are going to be choosy about what points you want to include the simplest thing is to build a new range of data.

**Domenic** · 06-20-2008, 08:42 AM

As Andy has already mentioned, it would be easier to build a new range of data. Otherwise, try the following...

1) Define (Insert > Name > Define) the following...

Array1:

=MIN(IF(ISNA($B$2:$B$20),ROW($B$2:$B$20)))-{1,2,3,4,5}

Array2:

=MAX(IF(ISNA($B$2:$B$20),ROW($B$2:$B$20)))+{1,2,3,4,5}

Array3:

=SMALL(IF(ISNUMBER(MATCH(ROW($B$2:$B$20),Array1,0))+ISNUMBER(MATCH(ROW($B$2:$B$20),Array2,0)),ROW($B$2:$B$20)-ROW($B$2)),{1,2,3,4,5,6,7,8,9,10})

2) Then try the following, which need to be confirmed with CONTROL+SHIFT+ENTER...

=LINEST(N(OFFSET(A2:A20,Array3,0,1)),N(OFFSET(B2:B20,Array3,0,1)))

and

=LINEST(N(OFFSET(B2:B20,Array3,0,1)),N(OFFSET(A2:A20,Array3,0,1)))

Note that only the 5 cells above the first #N/A and the 5 cells below the last #N/A will be analyzed. So the data can contain more than 2 #N/A's and all data between the first #N/A and the last #N/A will be ignored. See the attached file...

Hope this helps!

**DRFLETT** · 06-21-2008, 07:19 AM

Thats really helpful domenic.

I have about 20 columns of data to apply this to.

Do i just make 3 arrays for every column by hand?

I have noticed that the row numbers seem to change by themselves in the array definitions. Is that to be expected?

Andy

**Domenic** · 06-21-2008, 08:46 AM

Originally Posted by DRFLETT

I have about 20 columns of data to apply this to.

With many columns and a range which is likely to be large, you may find this solution rather slow. Do you still want to proceed? If so, attach a small sample of the data, about 20 rows by 20 columns, and place the actual results in the cells in which you'd like to see them?

Do i just make 3 arrays for every column by hand?

No, the references can be adjusted...

I have noticed that the row numbers seem to change by themselves in the array definitions. Is that to be expected?

I don't understand, can you elaborate?

**jinan0927** · 01-09-2014, 10:33 PM

But that doesn't deal with multiple regression. How can I do something similar with multiple x'ses?

**arlu1201** · 01-10-2014, 01:26 AM

jinan0927,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.