Using VBA to copy cells and removing blanks, then use Linest ()

Question

So here is my situation:

I've a large amount of data, with x values of 1-18, but y values that vary.. some of which are blanks because the equation is /0 - arranged in rows.

I am trying to get the m^3, m^2, m, c coefficients of the polynomial trend line for every row of y values.. y1, y2 etc.

Because I can not use Linest() with blanks I am trying to use VBA to copy out all the relevant (non-blank) y-values, but also removing the associated x-value for that row.

Here is an example section of the data - but there are more rows

x   1    2  3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  m3  m2  m   c
y1  0.0 1.0 2.0 2.0 1.0 0.0 1.0 -   2.0 2.0 1.0 2.0 2.5 1.0 1.0 2.0 1.0 4.0 ?   ?   ?   ?
y2  0.0 1.0 -   2.0 2.0 0.0 2.0 2.0 0.0 1.7 4.0 1.0 2.0 2.0 4.0 1.0 3.0 2.0 ?   ?   ?   ?

So far I have been trying without much success.

So far I have been trying something like this, but without success, but this is my first ever VBA attempts.. so sorry if I am unaware of the obvious!!

'code to identify known_y and known_x ranges
For each testvalue in known_y
If testvalue indicates that this point should be included in regression
   then copy this data point (both known_y and known_x value for this point) either into an array (if I want to invoke LINEST() from within VBA) or into another range in the spreadsheet.
Next testvalue
Invoke LINEST() using the copied data

If anyone could help it would be much appreciated!!

Can you please edit your question to include the intended result and what your example of data should look like after running the code. Is it removing the columns where x=3 & x=8 since y="-"? — CaptainABC, Jul 20 '14 at 17:34
Have made some edits. Yes, I am looking for the coefficients of the data arrays. But what I thought was the best approach was to use VBA to copy over the arrays but deleting the ones with blank values. That way it is then possible to use the Linest() function to get the coefficients. — Lozano, Jul 20 '14 at 20:16
@LaurenceBenson Thanks for the edits. Can you please show me how the result table should look after removing what you don't want and I will see if I can show you how it's done in VBA. — CaptainABC, Jul 20 '14 at 20:39

Ron Rosenfeld · Accepted Answer · 2014-07-21T00:17:20.197

Here is one approach using VBA. Since you start by looking for m3, I am assuming this is a third order polynomial. In any event, in the UDF myPolyLinest, the "order" is an argument named "maxPower. Other than that, it is similar to the worksheetfunction, and returns the same array of results. It filters out columns that don't have a numeric value. It does it for each set of Y values individually.

The formula on the worksheet would look like:

m3:  =INDEX(myPolyLinest($B2:$S2,$B$1:$S$1,3),1,1)
m2:  =INDEX(myPolyLinest($B2:$S2,$B$1:$S$1,3),1,2)
m1   =INDEX(myPolyLinest($B2:$S2,$B$1:$S$1,3),1,2)

where the arguments for the INDEX function are used to return the desired value I'm not sure what you mean by "c" coefficient as that is not described in LINEST Help for my version of Excel. But you should be able to figure it out from that file and the description of the return values.

Option Explicit
Function myPolyLinest(knownYs As Range, knownXs As Range, Optional maxPower As Long = 1, _
    Optional notForceZero As Variant = True, Optional Stats As Variant = False) As Variant
  Dim vX As Variant, vY As Variant
  Dim I As Long
  Dim J As Long
  Dim colXY As Collection

vX = knownXs
vY = knownYs

Set colXY = New Collection

For I = 1 To UBound(vX, 1)
    For J = 1 To UBound(vX, 2)
        If Not IsError(vY(I, J)) Then
            If vX(I, J) <> "" And vY(I, J) <> "" And _
                IsNumeric(vX(I, J)) And IsNumeric(vY(I, J)) Then
                colXY.Add Array(vX(I, J), vY(I, J))
            End If
        End If
    Next J
Next I

ReDim vX(1 To colXY.Count, 1 To maxPower)
ReDim vY(1 To colXY.Count, 1 To 1)


For I = 1 To colXY.Count
    For J = 1 To maxPower
        vX(I, J) = colXY(I)(0) ^ (J)
    Next J
    vY(I, 1) = colXY(I)(1)
Next I

myPolyLinest = WorksheetFunction.LinEst(vY, vX, notForceZero, Stats)

End Function

Using VBA to copy cells and removing blanks, then use Linest ()

1 Answers1