2

I have a table with four columns:

A: date

B: values

C: projection

D: short-term projection

The first two are hardcoded values. We admit that dates go up, while values mostly go down. I used a linear regression and a simple division so that projection displays the estimated date when value would hit 0.

The linear regression is done thanks to this formula:

=LINEST($B$5:$B23;$A$5:$A23)

5 being the first row in my table that contains valuable data.

If the regression is put in column E and F, projection is calculated by the following formula:

=-F23/E23

Column D is supposed to contain a projected date, but only dealing with the n last rows, and not since the beginning of my table. Assuming n=15, I removed some dollar signs for the linear regression so that only the last 15 elements are considered:

=LINEST($B8:$B23;$A8:$A23)

I would like this 15 to be a variable that I put in a cell and that I can easily change. Is that possible without using scripting? I tried to use INDIRECT, but I could not make it work the way I wanted.

I guess that I would need something like RANGE(A10;up;W17) where W17 is my n variable.

1 Answer 1

-1

How to select last N rows using the INDIRECT function

Imagine there's column A with an undetermined count of numeric rows, it is then possible to access the last n (specified in e.g. $F$2 cell) rows using the following function:

INDIRECT(CONCATENATE("A", COUNT(A:A) - $F$2 + 1, ":A", COUNT(A:A)))

For example, if A has 10 rows and $F$2 is 3, the CONCATENATE function above will return A8:A10, and the INDIRECT function will return the respective range as if A8:A10 range expression were hard-coded in the cell.

I tested it with SUM but it should also be possible to use this expression in the LINEST function required in the question:

=LINEST(INDIRECT(CONCATENATE("A", COUNT(A:A) - $F$2 + 1, ":A", COUNT(A:A))))

However, if the reason to do a linear regression on the last N rows only is because the dates and values slopes change ever so slightly with time, it might be advisable to add more features (e.g. binary 1 or 0: isWeekend, isWinter, isSummer, isAutumn), use standard scaling and cross-validation sampling and train a model on the entire data rather than just the last N rows, it is likely that the resulting model will have better quality.

How to do linear regression with standard scaling in Google Sheets

  1. Install TableTorch add-on for Google Sheets.
  2. Refresh your spreadsheet, click on the TableTorch icon on the right-side panel of Google Sheets.
  3. Select your dataset's range and click the Linear Regression item on the main menu of TableTorch.
  4. Click the Fit model button.
  5. Click the Insert prediction column button, it will create a column with a simple arithmetic formula that is readily usable for the new data.

Standard scaling is on by default for linear regressions done by TableTorch and cross-validation can be enabled in the sampling options, it usually improves robustness of the model.

P.S. I am the author of the mentioned add-on and will be glad to hear any feedback.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.