Linear Forecasting with criteria

[Dr Shrimp]

Hi everyone,

First time posting here, so sorry if the explanation/format of my question is unclear.

I'm trying to use the FORECAST.LINEAR function to come up with a very basic forecast for a product based off its Week 1 Sales. The sheet is set up as follows:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Product Name[/TD]
[TD]Sales period (in Days)[/TD]
[TD]Week 1 Sales[/TD]
[TD]Final/Total Sales
[/TD]
[TD]Sales Forecast[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD="align: center"]56[/TD]
[TD]8200[/TD]
[TD]240000[/TD]
[TD]244000[/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD="align: center"]49[/TD]
[TD]7100[/TD]
[TD]180000[/TD]
[TD]189000[/TD]
[/TR]
[TR]
[TD]Product 3[/TD]
[TD="align: center"]49[/TD]
[TD]5500[/TD]
[TD]166000[/TD]
[TD]165000[/TD]
[/TR]
[TR]
[TD]Product 4[/TD]
[TD="align: center"]49[/TD]
[TD]4500[/TD]
[TD]150000[/TD]
[TD]144000[/TD]
[/TR]
[TR]
[TD]Product 5[/TD]
[TD="align: center"]56[/TD]
[TD]4700[/TD]
[TD]183000[/TD]
[TD]180000[/TD]
[/TR]
[TR]
[TD]Product 6[/TD]
[TD="align: center"]49[/TD]
[TD]5000[/TD]
[TD]175000[/TD]
[TD]174000[/TD]
[/TR]
[TR]
[TD]Product 7[/TD]
[TD="align: center"]49[/TD]
[TD]4000[/TD]
[TD][/TD]
[TD]??????[/TD]
[/TR]
</tbody>[/TABLE]

For "Product 7" the Week 1 forecast formula (cell E7) is =FORECAST.LINEAR(C8,D2:D7,C2:C7). How would I modify this so that the forecast only takes into account products that were on-sale for the same period of time? (49 days in this case for Product 7)

I tried using an IF array formula that compares each product's sales period, but the end result still seems to include the two products that were on-sale for 56 days. I'm sure there is a very simple solution, I'm just not very familiar with the Forecast.linear function.

Any help would be appreciated!

 

[Worf]

Welcome to the Board

The example below shows the idea:

 

[ask2tsp]

If there is room for 2 helper columns you could do as follows

Excel Workbook
	A	B	C	D	E	F	G	H
1	Product Name	Sales period (in Days)	Week 1 Sales	Final/Total Sales	Sales Forecast		Filtered week 1 sales	Filtered Final/Total Sales
2	Product 1	56	8.200	240.000	244.000		-	-
3	Product 2	49	7.100	180.000	189.000		7.100	180.000
4	Product 3	49	5.500	166.000	165.000		5.500	166.000
5	Product 4	49	4.500	150.000	144.000		4.500	150.000
6	Product 5	56	4.700	183.000	180.000		-	-
7	Product 6	49	5.000	175.000	174.000		5.000	175.000
8	Product 7	49	4.000		120.865
Sheet