LN() function, excel 2010

[cwunderlich]

I have two ranges of data in excel (years are the "known x's" and data points are the "known y's"). I am trying to find the exponential trend using an excel formula.


Assume years are in range B2:B13 and data points are in range C2:C13.

I believe I found the following formula to work: =LINEST(LN(C2:C13),B2:B13)***

The problem is that I need to figure out how to do this analagous calculation in VBA. VBA already has each data range stored to a separate array. I tried the following syntax but I keep getting a "Type mismatch" error:

Trend = Application.WorksheetFunction.LinEst(Log(DataSet), Yrs) <- where Trend, DataSet, and Yrs are all declared as Double

can someone please help me with this.

***Also, part of my issue involves the following question: within excel, how does the LN(C2:C13) piece work exactly? I thought LN() argument could only take one data point as input.

Thank you.

 

[Norie]

What are DataSet and Yrs?

Ranges? Arrays?

 

[cwunderlich]

They are Arrays. At the beggining of the code I have them set up like this:

Dim Data() as Double
Dim Yrs() as Double

...then code here does some things and figures out how many elements should be put into these arrays and then comes back and does the following...

ReDim Data(0 To Application.WorksheetFunction.Count(YrsExcl) - 1)
ReDim Years(0 To Application.WorksheetFunction.Count(YrsExcl) - 1) '-where YrsExcl is just another array which has the final data set (with outliers excluded) that I want passed to use to determine how many elements Data() and Years() should hold.

...then the macro assigns values to each element of Data() and Years()...

Then I was thinking something like this:

Trend = Application.WorksheetFunction.LinEst(Log(DataSet), Yrs)

but that is where I get the type mismatch error.

 

[Norie]

Why aren't you using Application.WorksheetFunction.Ln?

 

[cwunderlich]

Why aren't you using Application.WorksheetFunction.Ln?

I had originally used that (with the same error message). But then I read somewhere that LOG() in VBA is the equivalent to the LN() worksheet function....

 

[Norie]

VBA's Log won't handle multiple values, whereas the worksheet function LN() will, under certain circumstances.

This actually works for me, but both calculations return 2 values

Dim ans As Variant
Dim ans2 As Variant
Dim DataSet As Variant
Dim Yrs As Variant

    DataSet = Range("C2:C13")
    Yrs = Range("B2:B13")
    
    ans = Application.LinEst(Application.Ln(Range("C2:C13")), Range("B2:B13"))
    
    ans2 = Application.LinEst(Application.Ln(DataSet), Yrs)