No points are missing a coordinate, no points have 3 coordinates (it's not a 3D chart is it?). Points on a chart each have 2 parts, an X coordinate and a Y coordinate. I don't know that the regression can handle an X (or Y) range on more than one column or row! "So my X range would be A1:last row of E" If you set a reference to atpvbaen.xls you can use the likes of: I tried using named arguments like inpyrng:= but it wasn't having it, so the arguments have to be in order Optional nplots As Variant, Normal Probability Plots true/false Optional lplots As Variant, Line Fit Plots true/false Optional rplots As Variant, Residual Plots true/false Optional sresiduals As Variant, Standardized Residuals true/false Optional residuals As Variant, Residuals true/false Optional soutrng As Variant, Output Range "" for a new worksheet letting excel name the sheet, "a new sheet" quote the name of the new sheet, nothing for a new workbook Optional confid As Variant, Confidence Level blank/nothing or 95 for 95%
Optional labels As Variant, Labels true/false Optional constant As Variant, Constant is Zero true/false Optional inpxrng As Variant, Input X Range Lr = Application.Max(lrA, lrC) 'or Min? I expect the x and y have to have the smae number of values?Īpplication.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("C1:C" & lr), ActiveSheet.Range("A1:A" & lr), False, True, 95, ActiveSheet.Range("M1"), False, False, False, False, ActiveSheet.Range("X3"), False LrC = Cells(Rows.Count, "C").End(xlUp).Row While you may be experiencing exponential decay, your rating system won't work with that.LrA = Cells(Rows.Count, "A").End(xlUp).Row Excel really isn't designed with this in mind and doesn't have the functions for this. I am a little concerned that your model may be fragile for the purposes of making a prediction. Is a road rated as a 10 actually 10 times better than one whose rating is a 1 and is one rated a 10 exactly 2 times better than one rated a 5? Your model also does not consider changes in weather or the comparability of building materials from twenty years ago to today.įinally, your dependent variable is probably ranked data. Although this loosely implies a quadratic-like model, the maximum value should always be for a new road and it is not under your specification. It seems like roads remain quite good for a long time, but once they begin deteriorating they begin to come apart faster and faster. It may be the case that no pre-built Excel model will be a good predictive fit.
For example, I am assuming that roads cannot spontaneously improve themselves.
My concern with your model is that you may be ignoring important mathematical properties and get bad predictions.
0 kommentar(er)
