CODEWITHSUNDEEP
vbaexceltrendline
Lozano
Lozano

Reputation: 153

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

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!!

Upvotes: 0

Views: 286

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60199

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

Upvotes: 1

Related Questions