Try searching for array formula to find the right version if you get stuck. The secret key combination for array formula may be different for older versions of excel, or for macs. If it didn’t work, there will be #VALUE everywhere. If this works, the block of cells should fill up with a bunch of numbers.
The first argument is the outcome variable data points (Y).
Of course during the Main() run, we could add code to return stat parts as needed.Īpplication.Run "ATPVBAEN.XLAM!Regress", Sheet2.Range("B2:B321"), _ I would that to make a formula lookup or a macro lookup, easier. SUMMARY OUTPUT Y1, SUMMARY OUTPUT Y2, etc. As you can see in the result on Sheet1, it regresses all x sets together in Main().Īs for looking up the stats after the run, I would probable add a unique value in Main(). To answer regress each y set vs one x set or the many x sets (16 max), you can run this code. I guess one could autofit after each regression or at the end of the Main() macro or separately as shown in the code here. LastColumn = rng.Find(What:="*", after:=rng.Cells(, ), _ 'Search for any entry, by searching backwards by Columns. If WorksheetFunction.CountA(Cells) > 0 Then Set rO = ws.Cells(1, LastNBCol(ws.Cells) + 2)Īpplication.Run "ATPVBAEN.XLAM!Regress", rY, _
If ws.ListObjects(1).Name "Independent" Then It is more involved due to listobjects.ĭim ws As Worksheet, rX As Range, rY As Range, rO As Rangeĭim lO As ListObject, yLO As ListObject, xLO As ListObject You can view those other ways by searching for "excel regress 16".įor question 2, after removing the data columns of more than 16 in that one listobject, you can try this code in a Module. If you want to do more, you will have to pursue other means. Regarding question 1 in post #5, no, 16 is the Regress limit.