VBa code for index(linest)
hi everyone
i m trying to write vba code to get coeefiecints of second order polynomial eequation Equation: y = (c2 * x^2) + (c1 * x ^1) + b c2: =INDEX(LINEST(y,x^{1,2}),1) C1: =INDEX(LINEST(y,x^{1,2}),1,2) b = =INDEX(LINEST(y,x^{1,2}),1,3) ihave recorded macro Cells(j + 2, "D").Select ActiveCell.FormulaR1C1 = _ "=INDEX(LINEST(RC[-2]:R[2]C[-2],RC[-3]:R[2]C[-3]^{1,2}),1)" Cells(j + 2, "E").Select ActiveCell.FormulaR1C1 = _ "=INDEX(LINEST(RC[-3]:R[2]C[-3],RC[-4]:R[2]C[-4]^{1,2}),1,2)" Cells(j + 2, "F").Select ActiveCell.FormulaR1C1 = _ "=INDEX(LINEST(RC[-4]:R[2]C[-4],RC[-5]:R[2]C[-5]^{1,2}),1,3)" but now trying to write VBA CODE for NROWS,NCOLUMNNS] i hop i wil get direction from this group many thanks in advance 4 ur efrts n time |
VBa code for index(linest)
Why not use this as your formula:
Cells(j + 2, "D").Resize(,3).FormulaArray = "=LINEST(RC[-2]:R[2]C[-2],(RC[-3]:R[2]C[-3])^{1,2})" What exactly are the NROWS and NCOLUMNS you want? NRows I guess is the span from R to R[2], so if you define a Long or Int variable nRows, the right part of the above becomes: "=LINEST(RC[-2]:R[" & nRows - 1 & "]C[-2],(RC[-3]:R[" & nRows - 1 & "]C[-3])^{1,2})" I don't know how you'd use nColumns in this context. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ wrote in message ups.com... hi everyone i m trying to write vba code to get coeefiecints of second order polynomial eequation Equation: y = (c2 * x^2) + (c1 * x ^1) + b c2: =INDEX(LINEST(y,x^{1,2}),1) C1: =INDEX(LINEST(y,x^{1,2}),1,2) b = =INDEX(LINEST(y,x^{1,2}),1,3) ihave recorded macro Cells(j + 2, "D").Select ActiveCell.FormulaR1C1 = _ "=INDEX(LINEST(RC[-2]:R[2]C[-2],RC[-3]:R[2]C[-3]^{1,2}),1)" Cells(j + 2, "E").Select ActiveCell.FormulaR1C1 = _ "=INDEX(LINEST(RC[-3]:R[2]C[-3],RC[-4]:R[2]C[-4]^{1,2}),1,2)" Cells(j + 2, "F").Select ActiveCell.FormulaR1C1 = _ "=INDEX(LINEST(RC[-4]:R[2]C[-4],RC[-5]:R[2]C[-5]^{1,2}),1,3)" but now trying to write VBA CODE for NROWS,NCOLUMNNS] i hop i wil get direction from this group many thanks in advance 4 ur efrts n time |
VBa code for index(linest)
What exactly are the NROWS and NCOLUMNS you want? NRows I guess is the span from R to R[2], so if you define a Long or Int variable nRows, the right part of the above becomes: thanx Mr. Jon for ur time n effort wel by nrows and n columns i mean i want to calculate equation coefficients for unknown set of X,y data plz stil if its unclear plz let me know many thanxin advance |
All times are GMT +1. The time now is 05:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com