Forecast Linear Function - Lookup & specify range through formula

MrCool888

New Member
Joined
Sep 23, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:
G18: =CELL("address";(INDEX($B$6:$F$13;MATCH($B15;$B$6:$B$13;0);2)))
H18: =CELL("address";(INDEX($B$6:$F$13;MATCH($B15;$B$6:$B$13;0);5)))

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
    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
Joined
Mar 22, 2020
Messages
1,020
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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)
1634464546613.png
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top