LN() function, excel 2010

cwunderlich

Board Regular
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

***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.

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Norie

Well-known Member
What are DataSet and Yrs?

Ranges? Arrays?

cwunderlich

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

Code:
``````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...

Code:
``````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:
Code:
``Trend = Application.WorksheetFunction.LinEst(Log(DataSet), Yrs)``
but that is where I get the type mismatch error.

Norie

Well-known Member
Why aren't you using Application.WorksheetFunction.Ln?

cwunderlich

Board Regular
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

Well-known Member
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
Code:
``````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)``````

Replies
3
Views
3K
Replies
8
Views
449
Replies
3
Views
752
Replies
5
Views
628
Replies
3
Views
263

1,195,632
Messages
6,010,788
Members
441,569
Latest member
PeggyLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back