Manual Calculation isn't being set

[ianfrancis56]

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.

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!

 

[shg]

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?

 

[ianfrancis56]

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?

 

[shg]

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

 

[ianfrancis56]

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

=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))

 

[mikerickson]

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

 

[shg]

=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.

 

[shg]

Are you doing mutiple linear regression, or polynomial regression?

 

[shg]

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))

 

[ianfrancis56]

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!