LINEST function with varying sample size

[flybyrob]

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.

 

[pgc01]

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]

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]

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.