How to forecast based on 3/5/7/10 etc. last observations?

[AlinaP92]

Hello everyone. 

I have two columns of observations: Date and Number of Registrations. 

My task is to forecast the number of registrations for the remaining days based on 3/5/7/10 last values. So the first time I have to pick values from May 19th till May 21st, the second time - from May 17th till May 21st, and so on. 

The problem is, that there is a cell with a fixed number of days I have to use. There is a dropdown list that should affect the forecast every time I change the value in it.

I've been struggling the whole day to adjust my forecast.linear() function so it takes into account these changes.

The file with the data is attached.

Would appreciate any help.

[#2 · Accepted solution]

@AlinaP92 

Select B23:B32.

Enter the formula

=FORECAST.LINEAR($A$23:$A$32,$B$22:OFFSET($B$22,-$E$2+1,0),$A$22:OFFSET($A$22,-$E$2+1,0))

If you have Microsoft 365, simply press Enter. Otherwise, press Ctrl+Shift+Enter.

[#3]

Thank you @Hans Vogelaar! That's what I wanted.

A quick question: OFFSET() function returns a value, not a cell address. How does Excel understand that I want B20 and not 7208950, also I got 7208950 applying OFFSET? It would be $B$22:7208950. 

[#4]

@AlinaP92 

OFFSET in fact returns a cell reference. For example, OFFSET(A1,2,0) returns a reference to cell A3.

In most uses, you will get the value of the cell it references to: if you enter the formula =OFFSET(A1,2,0) you will see the value of cell A3, just like when you enter the formula =A3.

But when constructing a range, you are using the cell reference returned by OFFSET.

A1:OFFSET(A1,2,0) is equivalent to A1:A3, not to A1:value_of_A3.

[#5]

Understood, thank you for the explanation.