Excluding 0s and blanks from a LINEST function

Hi All,

I know this is an old thread, but I wanted to contribute some code for getting r2 from LINEST using VBA. It's ugly, but it works.

Basically: Count through the two columns to be compared. If any row in either column is blank, skip it, otherwise copy the data for both columns to a temporary worksheet. Run LINEST on that temp worksheet and record the r2 value.

Sub LinestBlanksFixed()

Application.ScreenUpdating = False              'Don't update the screen
Application.DisplayAlerts = False               'Don't display alerts
Application.Calculation = xlCalculationManual   'Don't automatically calculate


Dim intCol1 As Integer          'Counts first column (x)
Dim intCol2 As Integer          'Counts second column (y)
Dim intRow As Integer           'Counts row within column
Dim intNonblank As Integer      'Counts non-blank rows within column
Dim varRegResults As Variant    'Linear Regression Results Array
Dim wLinestTemp As Worksheet    'Name of temporary worksheet created (and later deleted)

Set wLinestTemp = Worksheets.Add(After:=Worksheets(Worksheets.Count))

'Numbers used: 33 columns to be compared, each column has 22 rows, original data on Sheet 5, results written to sheet 10
For intCol1 = 1 To 33
        For intCol2 = 1 To 33
        intNonblank = 0
            For intRow = 1 To 22    'check each row
                If Not IsEmpty(Sheets(5).Cells(intRow + 4, intCol2 + 2)) And Not IsEmpty(Sheets(5).Cells(intRow + 4, intCol1 + 2)) Then 'if either x or y is blank in that row, skip it
                    intNonblank = intNonblank + 1
                    wLinestTemp.Cells(intNonblank, 1) = Sheets(5).Cells(intRow + 4, intCol1 + 2)
                    wLinestTemp.Cells(intNonblank, 2) = Sheets(5).Cells(intRow + 4, intCol2 + 2)
                End If
            Next
        varRegResults = Application.WorksheetFunction.Linest(Range(wLinestTemp.Cells(1, 1), wLinestTemp.Cells(intNonblank, 1)), Range(wLinestTemp.Cells(1, 2), wLinestTemp.Cells(intNonblank, 2)), True, True)
        Sheets(10).Cells(intCol2 + 2, intCol1 + 2).Value = Round(varRegResults(3, 1), 6)
        wLinestTemp.UsedRange.Clear
    Next
Next

Worksheets(Worksheets.Count).Delete


Application.DisplayAlerts = True 'Reset Alert Displays to true
Application.ScreenUpdating = True 'Reset Screen Updating to true
Application.Calculation = xlCalculationAutomatic

End Sub

I then use conditional formatting to highlight r2 values above a certain threshold.

Also note: this would also be a good reply to this thread, but that one's locked: http://www.ozgrid.com/forum/showthread.php?t=41284

If forcing to zero, the r2 value reported here is correct (in Excel 2003 and later only) and that in the manually created chart is wrong. http://support.microsoft.com/kb/829249/en-us

Enjoy!

Alex

And a follow-up: If all you're after is the "R2" value form Linest, that's exactly teh same as the RSQ function (and much easier to implement!) If you also want to F, dF, etc values, then Linest may still be of use, but there's probably an easier way to get it.

::bangs head on desk::