# Forecast Linear Function - Lookup & specify range through formula

#### MrCool888

##### New Member
Hi Mr Excel Forum,

I'm a new member and hope that you kind souls are able to help me with this hopefully easy question.

I'm trying to create a statistical forecasting function using the FORECAST.LINEAR function, and the idea is to work with high volumes of data so I believe the exact cell ranges of "known_ys" and "known_xs" needs to be looked up in some way in a big table instead of as is presented in below snippet.
As the input to "known_ys" is a range of cells, I have tried to replace the C9:F9, i.e. the "known_ys" with a combination of CELL/INDEX/MATCH to get the row as well as the start and end column of the range, but I don't get it to work for the FORECAST.LINEAR function by just replacing "C9:F9" with the output in cell G18 and H18. The idea is to do the same for the known_xs once I found a solution.

Formulas used in snippet:

Anyone can help me? I suppose I may be on the wrong track... See below for a snippet explaining the situation.

Kind regards,
MrCool888

#### Attachments

• ExcelProblem.PNG
24.3 KB · Views: 2

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### GraH

##### Well-known Member
Hi,
(sorry for not using XL2BB for the moment, I'm not on my home computer)

Not sure why you don't have the grand totals in your data table? Meaning, why would you have repeated part references? So I might be completely off here.
- So I did a sum first for the selected part number.
Excel Formula:
``=SUMIFS(C6:C13;\$B\$6:\$B\$13;\$B\$16)``
- Then used the forecast fx: x= value you want to forecast or predict, known Ys are the know values, known Xs are the known intervals, in your case the years. The iferror part is to have the first value repeated and not the error returned by forecast, since for the first value there are no known values.
Excel Formula:
``=IFERROR(FORECAST.LINEAR(C5;\$C\$16:C16;\$C\$5:C5);C16)``

Replies
4
Views
87
Replies
2
Views
547
Replies
3
Views
133
Replies
2
Views
109
Replies
6
Views
912

1,147,451
Messages
5,741,197
Members
423,648
Latest member
steel1968

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back