modellingman wrote:MDW1954 wrote:1nvest wrote:I tend to reference long term data, log scaled.
For a quickie indicator I plot the data, log scale the Y axis, and add a exponential trend line and tick the show R-squared option.
Bit of quote added by modellingman
linest(ln( .... range of values) provides a indicator of the trend line slope (more meaningful indicator of the CAGR). The R-squared value provides a indicator of the variability around the trend line, closer to 1.0 the closer individual data points aligned to the trend line.
Then I think, from what you've said, that you're performing logarithmic linear least squares.
MDW1954
@1nvest is indeed using a standard log linear regression model via use of the
LINEST() function on the log transformed data series. A slightly more direct approach is to use the
LOGEST() function(*). This provides the following (amongst other things)
- the least squares estimate of the growth rate
- the r-squared value
- the F-value for hypothesis testing of the growth rate
- the degrees of freedom
The square root of the F-value is the t-value and the square root of the latter is the correlation co-efficient. The p-value can be obtained by applying the
T.DIST.2T() function to the t-value. It is the same value as that obtained for the F-value by applying the
F.DIST.RT() function - ie both the t-value and F-value are related statistics for the hypothesis test of significance of the growth rate.
Apart from the graphs, the only real bits in the R/ggplot2 output not available through LOGEST() are the parameters of the quadratic equations which provide the confidence intervals for the expected and/or actual values of the dependent variable as a function of time. I'm reasonably sure these are not too difficult to find and calculate but I would have to do bit more dredging to get to that point.
MDW1954 wrote:The next stage in the project is to do a LibreOffice version, which will require turning Excel's VBA into LibreOffice BASIC. If anyone knows of any handy LibreOffice BASIC training/ user resources, now is the time to holler.
Not quite the holler you have asked for but...
With not much more than the LOGEST() function, it is possible to calculate the results. Following the model provided by the worksheet that you posted
here, formulae that can be used for the results cells are
Growth rate =INDEX(LOGEST(data,,TRUE,TRUE),1,1)-1
R_Squared =INDEX(LOGEST(data,,TRUE,TRUE),3,1)
Correlation =SQRT(R_Squared)
t_Value =SQRT(INDEX(LOGEST(data,,TRUE,TRUE),4,1))
Deg_Freedom =INDEX(LOGEST(data,,TRUE,TRUE),4,2)
p_Value =T.DIST.2T(t_Value,Deg_Freedom)
where
data
is the worksheet range containing the data values, for example B1:B17.
Although programming languages can extend what is possible using spreadsheets, my own preference is to use programming only when necessary. YMMV, but the above provides a fairly simple implementation without any need for programming. Further, this implementation is also compatible with Libre Office and Google Sheets.
I would agree that these formulae are not quite as straightforward or simple to apply in a workbook as what I'm sure are the UDF's that you created with IAAG's input. However, the Microsoft 365 version of Excel even allows this difficulty to be overcome without programming via use of the Name Manager and the
LAMBDA() function.
A workbook containing the formula above and also using friendly function names created via the LAMBDA function is available
here. To whet your appetite, as well as using the formula above, the Growth rate is also calculated using the formula
=LLLS.GrowthRate(B1:B17)
Similar LLLS functions are defined for the other results cells. The workbook will work on the web version of Excel (and will probably open automatically in it). Copies can also be downloaded for use with desktop products but the formulae labelled "Formula v1" will only work with the Microsoft 365 version of Excel on the desktop.
(*)Documentation of the LOGEST() function, both from Microsoft and other authors (eg
this and
this), is generally pretty weak on precisely how LOGEST() derives its estimates and completely fails to mention the relationship between LINEST() and LOGEST(). Microsoft does provide some insights in
this article, though the article is largely about correction of the algorithm for LINEST() which, in certain circumstances, was incorrect in versions of Excel prior to Excel 2003. Both the Microsoft article and my own experimentation confirm that LOGEST() effectively produces the same results as LINEST() after transforming the dependent variable (known y's in Microsoft terminology) using natural logarithms and then fitting a standard linear regression model using the transformed dependent variable and the untransformed (ie linear) independent variable (time or known x's in MS-speak). The co-efficients of this model are estimated using a least squares criterion and together these explain why the the term "logarithmic linear least squares" is an appropriate one.
modellingman