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:

Product Name	Sales period (in Days)	Week 1 Sales	Final/Total Sales	Sales Forecast
Product 1	56	8200	240000	244000
Product 2	49	7100	180000	189000
Product 3	49	5500	166000	165000
Product 4	49	4500	150000	144000
Product 5	56	4700	183000	180000
Product 6	49	5000	175000	174000
Product 7	49	4000		??????

<tbody>
</tbody>

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