Linest Ignore Blank Cells

[Oprichnick]

Hy,

I have data like:

X: 1 2 3 4 5 6 7 8 9 Forecasting

Y: 10 15 12 14 15 19 (linest^{1,2})


Is it possible to get Linest ignoring Y blanks and use only X values to which there are numeric values for Y?

I tried the following formula:

=LINEST(IF(ISNUMBER(Q2:Z2);Q2:Z2);(IF(ISNUMBER($Q$2:$Z$2);$Q$1:$Z$1))^{1;2})

Also tried Q2:Z2<>"";Q1;Z1

It also returns #VALUE!

Maybe this is not the best way to do it.

Thanks in advance,
Pedro

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=LINEST(INDEX(Q2:Y2,N(IF(1,MODE.MULT(IF(ISNUMBER(Q2:Y2),(COLUMN(Q2:Y2)-COLUMN(Q2)+1)*{1;1}))))),INDEX($Q$1:$Y$1,N(IF(1,MODE.MULT(IF(ISNUMBER(Q2:Y2),(COLUMN(Q2:Y2)-COLUMN(Q2)+1)*{1;1})))))^{1,2})

For your version of Excel, though, I think you'll need to use the following formula that also needs to be confirmed with CONTROL+SHIFT+ENTER...

=LINEST(INDEX(Q2:Y2;N(IF(1;MODE.MULT(IF(ISNUMBER(Q2:Y2);(COLUMN(Q2:Y2)-COLUMN(Q2)+1)*{1\1})))));INDEX($Q$1:$Y$1;N(IF(1;MODE.MULT(IF(ISNUMBER(Q2:Y2);(COLUMN(Q2:Y2)-COLUMN(Q2)+1)*{1\1})))))^{1;2})

Hope this helps!

 

[Oprichnick]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=LINEST(INDEX(Q2:Y2,N(IF(1,MODE.MULT(IF(ISNUMBER(Q2:Y2),(COLUMN(Q2:Y2)-COLUMN(Q2)+1)*{1;1}))))),INDEX($Q$1:$Y$1,N(IF(1,MODE.MULT(IF(ISNUMBER(Q2:Y2),(COLUMN(Q2:Y2)-COLUMN(Q2)+1)*{1;1})))))^{1,2})

For your version of Excel, though, I think you'll need to use the following formula that also needs to be confirmed with CONTROL+SHIFT+ENTER...

=LINEST(INDEX(Q2:Y2;N(IF(1;MODE.MULT(IF(ISNUMBER(Q2:Y2);(COLUMN(Q2:Y2)-COLUMN(Q2)+1)*{1\1})))));INDEX($Q$1:$Y$1;N(IF(1;MODE.MULT(IF(ISNUMBER(Q2:Y2);(COLUMN(Q2:Y2)-COLUMN(Q2)+1)*{1\1})))))^{1;2})

Hope this helps!

Hy,
Thanks for the reply.

It returns #N/A error

I can't figure out what it might be

 

[Domenic]

Which formula are you using? If you're using the second formula, the problem may lie in the separator used for the vertical array constant. In any case, have a look at the following sample workbook. When the workbook is opened, the formula should automatically be translated for your version of Excel, if necessary.

https://onedrive.live.com/redir?resid=41260C1C818928B2!662&authkey=!AMUcsi_GWMi_hH8&ithint=file,xlsx

Does this help?

 

[Domenic]

One other thought... for the cells containing your "Y" values... if they occur consecutively so that there are no blank cells between the relevant ones, you can use dynamic named ranges to define the relevant ranges... so the ranges for the formula will automatically adjust as data is added... and this way, there would be no need to use a complex formula like the one that I offered.

 

[Oprichnick]

Hy Domenic,
Thanks a lot. Your previous solution is perfectly working.
It was a "translation" problem

Best Regards

 

[Domenic]

You're very welcome. Glad I could help. So which language and version of Excel are you using? And how does you formula look like after it has been translated?

 

[Oprichnick]

I'm using 2010 and it is in English, but some bits are in european fashion. Like "," and ";".

I haven't completely understood what was the problem, but you gave a very simple and useful tool for future translation problems I'll have.

Cheers

 

[Domenic]

Oh, I see. So maybe your system uses a comma (,) instead of a backslash (\) for a vertical array separator. In any case, I'm glad you have what you need.

Cheers!

 

[HadtheSameProblem]

There are previous answers below and older threads to this but the solutions were too difficult for me to follow:

To make life simpler order your data first so that the blank y values always appear at the end, but you don't know how many there will be?

Assuming x values are in row 1 and the corresponding y values are in row 2 from cell E onwards then enter the following array formula across two neighbouring cells assuming at most 19 entries and it should select the correct range.

{=LINEST(CHOOSE(COUNT(E2:W2),E2,E2:F2,E2:G2,E2:H2,E2:I2,E2:J2,E2:K2,E2:L2,E2:M2,E2:N2,E2:O2,E2:P2,E2:Q2,E2:R2,E2:S2,E2:T2,E2:U2,E2:V2,E2:W2),CHOOSE(COUNT(E2:W2),E1,E1:F1,E1:G1,E1:H1,E1:I1,E1:J1,E1:K1,E1:L1,E1:M1,E1:N1,E1:O1,E1:P1,E1:Q1,E1:R1,E1:S1,E1:T1,E1:U1,E1:V1,E1:W1)) }

Note W corresponds to 19th entry, extend if you have more than 19 entries. Count(E2:W2) in 2nd part is not a typo as this assumes that there could be x values with blank y which you wish to ignore.

In practice if you always have the same x values, then use the $1 for row 1, and extend row 2 downwards for each set of y observations.