Forum Discussion
How to forecast based on 3/5/7/10 etc. last observations?
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.
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.
Thank you HansVogelaar! 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.
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.
- Understood, thank you for the explanation.
