Is there a limitation to the LINEST formula?
I am trying to write a LINEST formula that only considers a sub-set of cells in a cell-range using an array formula. With other formulas like AVERAGE or SUM, you can put an IF statement inside the formula to make it a conditional array. I get a #VALUE error when I try the following formula.
EXAMPLE:
Col B Col C Col D Col E
<TABLE style="WIDTH: 316pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=420 border=0 x:str><COLGROUP><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" span=4 width=105><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl25 style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; WIDTH: 79pt; BORDER-BOTTOM: #e1e0d2; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=105 height=18>Yvar</TD><TD class=xl25 style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; WIDTH: 79pt; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" width=105>Xvar1</TD><TD class=xl25 style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; WIDTH: 79pt; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" width=105>Xvar2</TD><TD class=xl25 style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; WIDTH: 79pt; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" width=105>"condition"</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl36 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18 x:num="6.0640002604725796">6.06</TD><TD class=xl39 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="2.6795443220907655">2.68</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="4.1583316364042222">4.16</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #cfcfcf" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="20.178225553293053">20.18</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="0.39033182896441687">0.39</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="26.301011592185098">26.30</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #cfcfcf" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="-4.9106011671118832">-4.91</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-6.4450337467578445">-6.45</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-0.30195550893199474">-0.30</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #f8e0b1" align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="7.2371394255833046">7.24</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="9.144838068630321">9.14</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="9.7962149216504599">9.80</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #cfcfcf" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="-17.987909092948961">-17.99</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-11.981313299356369">-11.98</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-0.67074041185943845">-0.67</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #f8e0b1" align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="-11.453620243026275">-11.45</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-12.921369983472021">-12.92</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="2.8476165210649107">2.85</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #f8e0b1" align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="0.66958530432343899">0.67</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-0.89887478383705588">-0.90</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="1.0628789946837021">1.06</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #cfcfcf" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="3.6489981403139646">3.65</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-3.6064954581183093">-3.61</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="5.5349837268448674">5.53</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #cfcfcf" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="4.5323560465889585">4.53</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="10.050169346329474">10.05</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-2.1993695288033077">-2.20</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #f8e0b1" align=right x:num>2</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl38 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18 x:num="4.541060381155571">4.54</TD><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:num="7.0781172117187703">7.08</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:num="9.4782379561131656">9.48</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #f8e0b1" align=right x:num>2</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18></TD><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl33 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #e9eee7; mso-ignore: colspan" colSpan=4 height=18 x:str="'=LINEST(IF(E2:E11=1,B2:B11),IF(E2:E11=1,C2:D11),TRUE,TRUE)">=LINEST(IF(E2:E11=1,B2:B11),IF(E2:E11=1,C2:D11),TRUE,TRUE)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
press ctrl+shift+enter to make array formula
The above formula gives me a #VALUE error. What am I doing wrong? I know that array formulas work with SLOPE and INTERCEPT but I can't use those, because I need to do multiple regression and return the ANOVA stats that LINEST provides.
I haven't been able to find anything that addresses this with my searches online. I know that I should just use a stats program to do this, but I don't know how to program in R or SAS or any of that stuff. Please help! Or tell me its impossible.
I am trying to write a LINEST formula that only considers a sub-set of cells in a cell-range using an array formula. With other formulas like AVERAGE or SUM, you can put an IF statement inside the formula to make it a conditional array. I get a #VALUE error when I try the following formula.
EXAMPLE:
Col B Col C Col D Col E
<TABLE style="WIDTH: 316pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=420 border=0 x:str><COLGROUP><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" span=4 width=105><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl25 style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; WIDTH: 79pt; BORDER-BOTTOM: #e1e0d2; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=105 height=18>Yvar</TD><TD class=xl25 style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; WIDTH: 79pt; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" width=105>Xvar1</TD><TD class=xl25 style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; WIDTH: 79pt; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" width=105>Xvar2</TD><TD class=xl25 style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; WIDTH: 79pt; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" width=105>"condition"</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl36 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18 x:num="6.0640002604725796">6.06</TD><TD class=xl39 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="2.6795443220907655">2.68</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="4.1583316364042222">4.16</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #cfcfcf" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="20.178225553293053">20.18</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="0.39033182896441687">0.39</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="26.301011592185098">26.30</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #cfcfcf" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="-4.9106011671118832">-4.91</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-6.4450337467578445">-6.45</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-0.30195550893199474">-0.30</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #f8e0b1" align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="7.2371394255833046">7.24</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="9.144838068630321">9.14</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="9.7962149216504599">9.80</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #cfcfcf" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="-17.987909092948961">-17.99</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-11.981313299356369">-11.98</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-0.67074041185943845">-0.67</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #f8e0b1" align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="-11.453620243026275">-11.45</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-12.921369983472021">-12.92</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="2.8476165210649107">2.85</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #f8e0b1" align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="0.66958530432343899">0.67</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-0.89887478383705588">-0.90</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="1.0628789946837021">1.06</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #cfcfcf" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="3.6489981403139646">3.65</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-3.6064954581183093">-3.61</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="5.5349837268448674">5.53</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #cfcfcf" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="4.5323560465889585">4.53</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="10.050169346329474">10.05</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" x:num="-2.1993695288033077">-2.20</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: #f8e0b1" align=right x:num>2</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl38 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18 x:num="4.541060381155571">4.54</TD><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:num="7.0781172117187703">7.08</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:num="9.4782379561131656">9.48</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #f8e0b1" align=right x:num>2</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18></TD><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl33 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #e9eee7; mso-ignore: colspan" colSpan=4 height=18 x:str="'=LINEST(IF(E2:E11=1,B2:B11),IF(E2:E11=1,C2:D11),TRUE,TRUE)">=LINEST(IF(E2:E11=1,B2:B11),IF(E2:E11=1,C2:D11),TRUE,TRUE)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
press ctrl+shift+enter to make array formula
The above formula gives me a #VALUE error. What am I doing wrong? I know that array formulas work with SLOPE and INTERCEPT but I can't use those, because I need to do multiple regression and return the ANOVA stats that LINEST provides.
I haven't been able to find anything that addresses this with my searches online. I know that I should just use a stats program to do this, but I don't know how to program in R or SAS or any of that stuff. Please help! Or tell me its impossible.
