# Manual Calculation isn't being set

#### ianfrancis56

##### New Member
Hi All,

In an attempt to speed up a macro, I am trying to enter the formulas into the cells and have calculation set the manual, however, it seems Excel is still calculating each cell.

Code:
``````Application.Calculation = xlCalculationManual
.Range("I2:I" & h).Formula = _
"=Sum(Index(LinEst(Indirect(""\$E\$4:\$E""&RC[-1])," & _
"Indirect(""\$A\$4:\$D""&RC[-1]), True, False), 1, 1)*R3C4," & _
"Index(LinEst(Indirect(""\$E\$4:\$E""&RC[-1])," & _
"Indirect(""\$A\$4:\$D""&RC[-1]), True, False), 1, 2)*R3C3," & _
"Index(LinEst(Indirect(""\$E\$4:\$E""&RC[-1])," & _
"Indirect(""\$A\$4:\$D""&RC[-1]), True, False), 1, 3)*R3C2," & _
"Index(LinEst(Indirect(""\$E\$4:\$E""&RC[-1])," & _
"Indirect(""\$A\$4:\$D""&RC[-1]), True, False), 1, 4)*R3C1," & _
"Index(LinEst(Indirect(""E4:E""&RC[-1])," & _
"Indirect(""\$A\$4:\$D""&RC[-1]), True, False), 1, 5))"

.Range("J2:J" & h).Formula = "=Index(LinEst(Indirect(""E4:E""&RC[-2])," & _
"Indirect(""A4:D""&RC[-2]), True, True), 3, 1)"

.Range("M2:M" & h).Formula = _
"=Sum(Index(LinEst(Indirect(""\$F\$4:\$F""&RC[-1])," & _
"Indirect(""\$A\$4:\$D""&RC[-1]), True, False), 1, 1)*R3C4," & _
"Index(LinEst(Indirect(""\$F\$4:\$F""&RC[-1])," & _
"Indirect(""\$A\$4:\$D""&RC[-1]), True, False), 1, 2)*R3C3," & _
"Index(LinEst(Indirect(""\$F\$4:\$F""&RC[-1])," & _
"Indirect(""\$A\$4:\$D""&RC[-1]), True, False), 1, 3)*R3C2," & _
"Index(LinEst(Indirect(""\$F\$4:\$F""&RC[-1])," & _
"Indirect(""\$A\$4:\$D""&RC[-1]), True, False), 1, 4)*R3C1," & _
"Index(LinEst(Indirect(""F4:F""&RC[-1])," & _
"Indirect(""\$A\$4:\$D""&RC[-1]), True, False), 1, 5))"

.Range("N2:N" & h).Formula = "=Index(LinEst(Indirect(""E4:E""&RC[-2])," & _
"Indirect(""A4:D""&RC[-2]), True, True), 3, 1)"``````

Any insight? Thanks!

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I'm surprised that works at all.

Why are you calculating LinEst 5 times to get 5 coefficients rather than once?

What's the resulting formula?

How could I calculate it fewer times and still have the coefficient multiply by each variable? There are 4 predictor variables, then the constant. I'm using index to point to each coefficient which is then multiplied by the correct variable. I see what you're saying, but I can't think of a way to do it?

Last edited:
I don't know. That's why I asked what the final formula in the cell is.

i don't know. That's why i asked what the final formula in the cell is.

Code:
``=sum(index(linest(indirect("\$e\$4:\$e"&h2),indirect("\$a\$4:\$d"&h2), true, false), 1, 1)*\$d\$3,index(linest(indirect("\$e\$4:\$e"&h2),indirect("\$a\$4:\$d"&h2), true, false), 1, 2)*\$c\$3,index(linest(indirect("\$e\$4:\$e"&h2),indirect("\$a\$4:\$d"&h2), true, false), 1, 3)*\$b\$3,index(linest(indirect("\$e\$4:\$e"&h2),indirect("\$a\$4:\$d"&h2), true, false), 1, 4)*\$a\$3,index(linest(indirect("e4:e"&h2),indirect("\$a\$4:\$d"&h2), true, false), 1, 5))``

Regarding manual calculation, placing a formula in a cell triggers Calculation in all calculation modes.

Code:
``````=SUM(
INDEX(LINEST(INDIRECT("\$E\$4:\$E" & H2), INDIRECT("\$A\$4:\$D" & H2), TRUE, FALSE), 1, 1)*\$D\$3,
INDEX(LINEST(INDIRECT("\$E\$4:\$E" & H2), INDIRECT("\$A\$4:\$D" & H2), TRUE, FALSE), 1, 2)*\$C\$3,
INDEX(LINEST(INDIRECT("\$E\$4:\$E" & H2), INDIRECT("\$A\$4:\$D" & H2), TRUE, FALSE), 1, 3)*\$B\$3,
INDEX(LINEST(INDIRECT("\$E\$4:\$E" & H2), INDIRECT("\$A\$4:\$D" & H2), TRUE, FALSE), 1, 4)*\$A\$3,
INDEX(LINEST(INDIRECT("E4:E"&H2),        INDIRECT("\$A\$4:\$D" & H2), TRUE, FALSE), 1, 5))``````
What purpose does the INDIRECT serve? It makes the formula volatile, which is brutal.

A3:D3 presumably contain something like x^4, x^3, x^2, x.

It seems to me like you could instead use something like

=SERIESSUM(\$D\$3, 4, -1, LINEST(E1:E100, A1:D100^{1,2,3,4}))

... for D3<>0.

Are you doing mutiple linear regression, or polynomial regression?

If it's multiple regression, then something like

=SUMPRODUCT(LINEST(\$E\$4:INDEX(E:E, H2), \$A\$4:INDEX(D:D, H2)), CHOOSE({1,2,3,4,5}, \$D\$3, \$C\$3, \$B\$3, \$A\$3, 1))

I'm doing a multiple linear regression, yes. Indirect serves as the row number, I am running through multiple ranges to see which range gives the best regression (lowest R^2)

Thanks for the formula! I'll give it a try right now!

Replies
16
Views
504
Replies
12
Views
410
Replies
2
Views
130
Replies
5
Views
129
Replies
3
Views
260

### Forum statistics

1,203,241
Messages
6,054,320
Members
444,717
Latest member
melindanegron ### 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