![]() I'm going to test whether Application.displayAlerts=False will work for suppressing the overwriting question. ![]() There is every chance the X and Y ranges are not the same size, and this is what I think the line should look like:Īpplication.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("F1:F" & lr), ActiveSheet.Range("A1:A" & lr), False, True, 95, ActiveSheet.Range("P1"), False, False, False, False,, False (although I would seriously consider using Min)Īpplication.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("F1:F" & lr), ActiveSheet.Range("A1:A" & lrE), False, True, 95, ActiveSheet.Range("P1"), False, False, False, False, ActiveSheet.Range("X3"), False If you're plotting column A against column F then you've correctly got I haven't the foggiest what part they play. Now you've introduced columns B, D and F. Since I plumped for max, then there would be some blank cells in one of the ranges - more fool me, I should have gone for min, that way there'd be no blanks at the bottom of either column that I didn't know how the regression tool handled. 'or Min? I expect the x and y have to have the same number of values? Note the comment I made on the same line: Which takes the larger of the two values and puts it into lr, with the intention of using it for both X and Y ranges (Columns A and C) for the regression calculation. So I had to use a row number which was the same for both column A and C, which gave rise to the line: To my mind (to anyone's actually), if these values were different then you wouldn't have a pair of coordinates for each point. To find the bottom cell with anything in it in column C. To find the bottom cell with anything in it in column A and the line: This is why in my code suggestion I had the line: So as a general rule of thumb, for a 2D chart, you always have pairs of coordinates so it's best if those pairs have values you're sure to want included in the regression calculation (rather than having random blank values) and that there should be an equal number of X and Y coordinates. That's absolutely fine if you want blank cells to be counted as zeroes but be aware that it will affect the regression. But which value pairs up with A6, A7 etc.? Blank cells? I don't know how the regression tool handles blanks, perhaps it calls them zeroes. If they are different sizes let's say the bottom of column A was row 10 so the range for say the X-coordinates is A1:A10, and the bottom of row C is row 5 so the range for the Y coordinates is C1:C5, that's great, we have pairs of coordinates for the first 5 points. So I'd hazard a guess that the number of X coordinates should be the same as the number of Y coordinates, which means equal size ranges of the 2 columns being used for the X and Y ranges. 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 LrA = Cells(Rows.Count, "A").End(xlUp).Row
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |