FORECAST using MATCH criteria

[shavar]

Hi everyone. I'm trying to build a formula using MATCH and FORECAST. I suspect this will need to be an array formula. The data set shows a % Fill for each ID and each week (Monday). For example ID AAH00 lists four dates each with an associated % Fill. ID XABB1 shows similar information for the same dates. The reason I'm using a formula rather than just charting it is that the data I get is minimum 400+ rows long with several different IDs each week. Each week a new entry is inputted for each ID. So it builds on each week.

Please see the attached for some work I've done and the results I would like in sheet Result Goal. I hope this will give you a better idea of what I'm after. Any suggestions would be appreciated.

forecasting based on match.xlsx

[p24leclerc]

Try this formula in E2 and copy it down.

Please Login or Register  to view this content.

[shavar]

Hello. I appreciate your response. Your formula works by referencing existing sheets. I included the two forecast sheets for two separate IDs to show how I came up with the forecast results of 85% and 87%. In reality, there will just be a single data sheet. I apologize if that was misleading. Great formula though that I will find a use for.

The result goal sheet is how the original data would like minus the Next Period Forecast column. The formula would calculate the forecast for the next period using WeekNum and % Fill on the Result Goal sheet. There would be no other sheets or references in the original data set. I'm thinking the formula would find every instance of a specific ID in column ID and hold its associated list of WeeNum's and % Fill's in an array and then perform a forecast using the MAX of WeekNum + 1 as x for the ID. The list of % Fill's would be the known_y's and the list of WeekNum's would be the known_x's.

I've re-attached file with sheets removed and only showing how the data looks when I get it and the result goal. Thank you for your time.

forecasting based on match (2).xlsx