# LINEST function with varying sample size

#### flybyrob

##### New Member
I am trying to perform regressions using the LINEST function and can perform this task if I know the array sizes to use in the formula (ex. =LINEST(A1:A100,B2:D100,,1) ). The sample sizes vary on each run, so one might have 100 sets of data like in the example above, the next might have 200 sets of data (ex. =LINEST(A1:A200,B2:D200,,1) ).

I tried to use a formulas inside the functions (e.g. CONCATENATE) to put in the number of data sets in a particular run, but I get an error every time. Are you able to use other functions inside the LINEST function??

Not trying to do anything fancy here, just trying to pull a different size array without having to re-type the number in the formula each time. I would like to use a formula and avoid VBA scripts if possible.

Thanks for any help.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### pgc01

##### MrExcel MVP
Hi
Welcome to the board

Yes, you can use other functions inside LINEST(). This is an example using the formula you posted, for a variable number of rows.

Assuming that in A:D you just have the table with the values that you use in LINEST(), use:

=LINEST(A1:INDEX(A:A,COUNT(A:A)),B1:INDEX(D:D,COUNT(A:A)),,1)

#### flybyrob

##### New Member
Thanks pgc01. I have tried using COUNT, CONCATENATE, and AND functions inside the LINEST function and I get the same error with each one. It does, however, work with IF functions. But, I am not sure how to make an IF statement to put a specific array size in there.

Is there a setting or something that I need to enable to be able to use other functions inside LINEST?

#### pgc01

##### MrExcel MVP
Well, I don't really understand what you mean. I've always used functions inside LINEST() with no problem.

It would be easier if you'd post a working example

- some data (4 -5 rows would be enough)
- what you want to calculate
- the formula you are using that returns an error
- the expected result

This would allow us to better understand what's happening.

Replies
3
Views
2K
Replies
2
Views
2K
Replies
11
Views
467
Replies
1
Views
104
Replies
6
Views
147

1,191,274
Messages
5,985,698
Members
439,974
Latest member
sjoerdbosch

### 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.

### Which adblocker are you using?

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

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