Adjusted R Squared with VBA
 

I have two functions I've written to calculate adjusted r-squared.
The notes in the function headers tell the limitations of each. An ideal
solution would be one ADJRSQ function that works with non-contiguous X
variables AND can handle multiple regressions.

Any help is greatly appreciated. I'm thinking it can't be done, but maybe
someone out there has an idea.

Thanks!
-Jeremy

==================================================
Function ADJRSQ(Ys As Range, Xs As Range)
'Calculates Adjusted R-squared and functions exactly
'like the native RSQ function. i.e.- doesn't need
'contiguous data or even data on the same worksheet.
'Will not work with multiple Xs.

Dim Rsquare As Double
Dim MaxCount As Long

On Error GoTo FoundError
With Application.WorksheetFunction
MaxCount = .Count(Ys)
If .Count(Xs) < MaxCount Then MaxCount = .Count(Xs)
Rsquare = .RSq(Ys, Xs)
ADJRSQ = 1 - (MaxCount - 1) * (1 - Rsquare) / (MaxCount - 2)
End With
Exit Function

FoundError:
ADJRSQ = CVErr(xlErrValue)

End Function

Function ADJRSQ_Mult(Ys As Range, Xs As Range)
'Calculates Adjusted R-square for multiple regressions.
'Limited functionality just like the native Analysis Toolpak
'regression analysis; data must be on same sheet and contiguous.

Dim ErrorTerm As Double

On Error GoTo FoundError
With Application.WorksheetFunction
ErrorTerm = .Index(.LinEst(Ys, Xs, , True), 3, 2)
ADJRSQ_Mult = 1 - (ErrorTerm / .StDev(Ys)) ^ 2
End With
Exit Function

FoundError:
ADJRSQ_Mult = CVErr(xlErrValue)

End Function
==================================================