How to skip a column in a matrix VBA

[ybenali]

hi everyone,
My program is running a multiple linear regression. The input is a vector "y" and a matrix "x" made for five columns (x1,x2,x3,x4,x5).
The following line is the call of my the multiple linear regression :

ActiveCell.FormulaR1C1 = _
"=LINEST(RC[-8]:R[4]C[-8],RC[-7]:R[4]C[-3],FALSE,FALSE)"

Here is the little trouble: I would like to choose only two columns for my linear regression "x1" and "x3", and not the whole matrix ! I need to skip the second column "x2".

if anyone have a clue, it would be most helpful !!
Thanks !!

 

[Andrew Poulsom]

Welcome to the Board.

Sorry, LINEST doesn't accept noncontiguous ranges:

http://support.microsoft.com/kb/79625

 

[ybenali]

Thank you !!

I had noticed that ! but can't we set a little sub to unite two columns "x1" and "x3" , and then we'll have a matrix of two columns instead of five columns ?

 

[pgc01]

Hi ybenali
Welcome to the board

Linest() does not accept non-contiguous ranges. You can, however, create an array on the fly using Choose():

ActiveCell.FormulaR1C1 = _
"=LINEST(RC[-8]:R[4]C[-8],CHOOSE({1,2},RC[-7]:R[4]C[-7],RC[-5]:R[4]C[-5]),FALSE,FALSE)"

Reamark: I'm just giving you a syntax solution for this specific case. With more information maybe there's a better way to do it.

 

[ybenali]

Thank you !!

I think that it will work this way !!

Best regards.