Need assistance with VBA (array creation) for logarithmic regression forecast UDF

LRobert

New Member
Joined
Nov 12, 2019
Messages
3
MY GOAL:
My goal is to create a uniform set of UDFs that perform forecasts under various forms of regression, such as exponential, logarithmic, and power. Excel has several native functions for forecasting under various forms of regression, but there are several and the syntax is not uniform.

I created this UDF for forecasting using linear regression to serve as an example here:

Function Forecast_Lnr(X As Range, knownYs As Range, knownXs As Range)
Forecast_Lnr = Application.Forecast(X, knownYs, knownXs)
End Function​

This is identical to Excel’s native function:

=FORECAST(X, knownYs, knownXs)​

I would like to create a similar UDF for forecasting using exponential regression and will use the syntax for other UDFs. This requires a natural log transform of Range knownXs. Excel’s native formula for forecasting using exponential regression is:

=FORECAST(LN(X), knownYs, LN(knownXs))​

MY PROBLEM:
I am having difficulty translating the indicated native formula to VBA. Specifically, I am having difficulty creating an array that is the natural log transform of Range knownXs represented above as LN(knownXs). I started with this function:

Function Forecast_Log(X As Range, knownYs As Range, knownXs As Range)
Forecast_Log = Application.Forecast(Application.Log(X), knownYs, Application.Log(knownXs))
End Function​

and found it did not work. I then took an honest shot at building an array for LN(knownXs) with a loop and still could not get the UDF working.

MY ASK:
I am requesting help with creating an interim step for Forecast_Log() that loops through the knownXs input and creates an array of their natural logarithms. Alternatively, I simply need assistance with Fixing Forecast_Log(). You help will be greatly appreciated.
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,435
... and found it did not work...
Could you please provide more detail on what exactly was not working?

Meanwhile, try
Code:
Function Forecast_Log(X As Range, knownYs As Range, knownXs As Range)
    Forecast_Log = Application.Evaluate("FORECAST(LN(" & X.Address & ")," & knownYs.Address & ",LN(" & knownXs.Address & "))")
End Function
 

LRobert

New Member
Joined
Nov 12, 2019
Messages
3
Could you please provide more detail on what exactly was not working?

Meanwhile, try
Code:
Function Forecast_Log(X As Range, knownYs As Range, knownXs As Range)
    Forecast_Log = Application.Evaluate("FORECAST(LN(" & X.Address & ")," & knownYs.Address & ",LN(" & knownXs.Address & "))")
End Function

Thank you! Worked like a charm!
 

Forum statistics

Threads
1,077,618
Messages
5,335,267
Members
399,009
Latest member
twcaddell

Some videos you may like

This Week's Hot Topics

Top