How to set up spreadsheet for regression analysis in Excel 2016

[waimea]

Hi Kenneth,

thank you again for your reply and for the updated code. I am looking at Main() right now and I'll try to change it bit by bit.

[Paul_Hossler]

Late to the game, but it might be easier if you used LINEST in the macro to gernerate the regression coefficents -- either just the coeffecients or more results (see help)

Example and Analysis Toolpack compare in first sheet

I also earlier created a wrapper function that returned similar data in an array -- second sheet

Either way, the macro can receive the outputs from the function and then put on WS the way you want to format for showing the results


I just put X1-X3 and Y1-Y8 data onto a single sheet to make it easier for me to see

[waimea]

Hi Paul, thank you for your reply.

This is impressive and there is a lot of code to understand, my jaw dropped when I looked at the module.

The 1st sheet "Using WS functions" works but the 2nd sheet "Using VBA function" the coefficents in "O2:R9 " says "#VALUE!"

[Paul_Hossler]

What version of Excel are you using?

This is an array-entered formula

Capture.JPG

I'm not sure how many fitting parameters, you need, but LINEST in your macro would probably be easier to deal with that the Analysis addin

Maybe post a sample of what you'd like to end up with

[waimea]

Hi Paul,

I am using Office 365 Pro Plus.

I realized now that another user also must enable the Data Analysis VBA Toolpak to gain access to the functions which is an unnecessary step!

I am not sure that I would end up with too be honest, it is a long time since I took stats in university.

I want to check which variables that are significant and in some way automate this procedure by comparing the P-value with Significance F.

I have 100+ worksheets with X variables and one worksheet with Y variables, the goal is to find out which variables that are significant and use their coefficients when calculating a weighted score.

What output would you choose from the macro?

[waimea]

How can I use LINEST in the macro provided earlier in this thread by Kenneth?

[Kenneth Hobs]

The advantage to the formula array method is that your data will update if data is changed. It will not update fully if you add a row.

1. Do a few manual formula arrays first to see how that is laid out. You need to know how many columns and rows to use for the array in the automated formula method.
2. Then build your formula string for a resized rO's formula. Resize based on findings from (1). This replaces the Regress line of code.

If (2) is too difficult, just do (1) manually and attach the file.

[waimea]

Hi Kenneth,

at the moment I am using your code and I want to understand your code fully before I change from Regress to LINEST.

I am working on an user form for your code where I can select tables to regress on, select independent and dependent variables.

How would you structure such a user form?

[Kenneth Hobs]

If you can't be sure that the add-in is set, I am not sure how that would help. It would work if you used Linest().

If you were to use Regress and needed to open the dialog for the user, we could probably do that. Of course they could too as it is in the ribbon.

In both cases, setting the inputs in a coded dialog (userform) would probably make use of Application.Inputbox(), or 3 calls to it in Main(). You would need 3 calls for x, y, and output. For the x and y, the prompt would be to select maybe the top cell(s) for x and just one top cell for y and output cells. The macro would then reset the select to the top to the bottom. That way, x1 or other single or x1toX16 could be set for Xs.

[waimea]

Hi, thank you for your reply. That user form sounds really complicated.

You are correct on the linest() part but I have to crawl before I can walk.

I can't say that I really understand the difference between regress using data analysis toolpak and Linest() ?

[Paul_Hossler]

Example of using LINEST

Option Explicit


Sub UseLinEst()
    Dim rData As Range
    Dim rX As Range, rY As Range
    Dim iY As Long
    Dim vLinEst As Variant
    
    Set rData = Worksheets("Using LINEST").Cells(1, 1).CurrentRegion
    
    With rData
        Set rData = .Cells(2, 1).Resize(.Rows.Count - 1, .Columns.Count)
    End With

    Set rX = rData.Columns(2).Resize(, 3)

    For iY = 6 To 12
        Set rY = rData.Columns(iY)
    
        vLinEst = Application.WorksheetFunction.LinEst(rY, rX, True, False)
        
        Worksheets("Using LINEST").Cells(iY - 4, 15).Value = vLinEst(1)
        Worksheets("Using LINEST").Cells(iY - 4, 16).Value = vLinEst(2)
        Worksheets("Using LINEST").Cells(iY - 4, 17).Value = vLinEst(3)
        Worksheets("Using LINEST").Cells(iY - 4, 18).Value = vLinEst(4)
    
    Next iY
End Sub

[Kenneth Hobs]

You might want to play around with =LinEst() using an array formula. Select a block of x columns plus 1 and 6 rows, add the formula, and press Shift+Ctrl+Enter. I added this manual example to Paul's last example file.

You need to understand what stats it gives vs. Regression method to see if it will meet your needs. See:
https://support.office.com/en-us/art...rs=en-US&ad=US

I added some pseudo data to make more than 16 x sets. I am not sure how reliable =LinEst() is past 16 but it seems to work I suppose.

[waimea]

Hi Kenneth,

thank you for your reply! I am going to read your link and then decide if I should use your macro and regress or LinEst.

If I wanted to add text to every output in main, example. summary output 1,2,3 etc. How would I go about?