Producing mutivariate statistics in Excel for a known y and known x's

[jeffreybrown]

Could somebody point me in the right direction for producing multivariate statistics in Excel?

I was given these functions for bivariate statistics, but I can't seem to find anything for multivariate statistics.

Parabola: =LINEST(y, x^{1,2})
Power: =LINEST(LX(y), LN(x))
Exponential: =LINEST(LN(y), x)

[shg]

=linest(a1:a100, b1:d100)

ai = r*bi + s*ci + t*di + u

[jeffreybrown]

Thank you shg.

As I'm thinking about how to implement this, is there anyway to build a range off of user input?

Example:
The y's are in B4:B29
The x's are in column C4:H29
The user may want to test different x's in the equation, so is there a way to have the user double click in that particular column and only have those ranges added into the known_x's argument of the Linest function?

For the double click method on row two I'm going to use...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Limit Target count to 1
    If Target.Count > 1 Then Exit Sub
    'Isolate Target to a specific range
    If Intersect(Target, Range("myChecks")) Is Nothing Then Exit Sub
    'Set Target font to "marlett"
    Target.Font.Name = "marlett"
    'Check value of target
    If Target.Value <> "a" Then
        Target.Value = "a" 'Sets target Value = "a"
        Cancel = True
        Exit Sub
    End If
    If Target.Value = "a" Then
        Target.ClearContents 'Sets Target Value = ""
        Cancel = True
        Exit Sub
    End If
End Sub

[shg]

I'd try to sort this from the UI first, then automate it.

For example, you could do

=LINEST(B4:B29, CHOOSE({1,2}, C4:C29, E4:E29))

You could aggregate the selected columns into an array.

I don't have time to help on this right now, Jeffrey, but if you hack away, I'll look in ...

[jeffreybrown]

That sounds good. I'll see what I can come up with.

[jeffreybrown]

Hi shg,

Thanks again for the lead in the right direction and I have worked out a preliminary way to solve my dilema, but I am going to have to give this much more thought and get some user input to have they want to see it set up. I am going to mark this thread as solved as the OP was answered.

[shg]

Depending on the requirement ...

You could allocate the row above the Marlett checkboxes (which are above the independent values) for coefficients. When a checkbox changes, clear the coefficients row, aggregate the selected x values into an array, run the regression, and then mete out the coefficients to the appropriate columns.

[jeffreybrown]

This sounds like a good option, but not sure on how to write the values into an array. This I will have to look into because the best I could see to do by myself would be to use the Marlett checkboxes to copy a checked column one by one into new columns and then run the Linest off of the new column. Lots of things to consider and test.