LN() function, excel 2010

cwunderlich

Board Regular
Joined
Sep 24, 2010
Messages
101
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
What are DataSet and Yrs?

Ranges? Arrays?
 
Upvote 0
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.
 
Upvote 0
Why aren't you using Application.WorksheetFunction.Ln?
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top