Forecast in Excel with multiple criteria

[JS050]

Hi everyone,

I am continuing on an earlier post which AhoyNC solved about finding an value in a range that is between percentage of one 1 value: Value within percentage range

Now, I am trying to forecast future values based on multiple criteria within 2 ranges. I have tried to use XL2BB, but I can't get it to work, apologies in advance.

I have created a summary table in N5:P10 of the values in H5:L10. This is obviously based on the first table. In this summary I was able to get a forecast with =FORECAST(B2;K6:K16;J6:J16), which was easy.

However, now I am trying to get a forecast based on two different Criteria: B1 & B2 in cell E22 for the Price in EU en F22 for the Price in SEA. In B1 i have created a drop-down menu with all the unique types of wood in C6:C16. In B2 is 3600 which need to correspond to the values of D6:D16. I would like to get a forecast with for example, only oak. It should give me then a forecast of Price in EU & Price SEA based on the C7:F7;OAK, 3000,1500,975 & C13:F13; 4000,2500,1300.

Can somebody help?

Thanks a lot in advance!

 

[AhoyNC]

Not sure I totally understand, but maybe:
On earlier versions of Excel you will probably need to enter this as an array formula. Which is CTRL-SHIFT-ENTER in windows. On a Mac I think it's COMMAND-RETURN.
Also, since you stated before you don't have Excel 365 at work you need to advise us what version you are using since your profile has 365.

Book1
	A	B	C	D	E	F	G
1	Type	Oak
2	Weight	3600
3	Procentual Change	95%	3420
4		105%	3780
5		Unique ID Type		Weight	Price EU	Price SEA
6		ffff	Pine	3700	2,000	1,300
7		Balt	Oak	3000	1,500	975
8		PPRR	Walnut	3240	1,500	975
9		KB	Pine	3650	1,600	1,040
10		FDD	Ash	3240	1,800	1,170
11		FFGG	Ash	3700	1,900	1,235
12		KKKK	Pine	3450	1,900	1,235
13		EEDD	Oak	3700	2,000	1,300
14		EECII	Walnut	4000	2,500	1,625
15		EEFFF	Walnut	4300	1,900	1,235
16		CCMM	Pine	4200	2,500	1625
17
18				Type	Weight	Price EU	Price SEA
19
20
21
22			Forecast	Oak	3600	1929	1254
Sheet2

Cell Formulas
Range	Formula
C3	C3	=B2*B3
C4	C4	=B2*B4
F22	F22	=FORECAST($B$2,IF($C$6:$C$16=$B$1,$E$6:$E$16),IF($C$6:$C$16=$B$1,$D$6:$D$16))
G22	G22	=FORECAST($B$2,IF($C$6:$C$16=$B$1,$F$6:$F$16),IF($C$6:$C$16=$B$1,$D$6:$D$16))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JS050]

Not sure I totally understand, but maybe:
On earlier versions of Excel you will probably need to enter this as an array formula. Which is CTRL-SHIFT-ENTER in windows. On a Mac I think it's COMMAND-RETURN.
Also, since you stated before you don't have Excel 365 at work you need to advise us what version you are using since your profile has 365.

Book1
	A	B	C	D	E	F	G
1	Type	Oak
2	Weight	3600
3	Procentual Change	95%	3420
4		105%	3780
5		Unique ID Type		Weight	Price EU	Price SEA
6		ffff	Pine	3700	2,000	1,300
7		Balt	Oak	3000	1,500	975
8		PPRR	Walnut	3240	1,500	975
9		KB	Pine	3650	1,600	1,040
10		FDD	Ash	3240	1,800	1,170
11		FFGG	Ash	3700	1,900	1,235
12		KKKK	Pine	3450	1,900	1,235
13		EEDD	Oak	3700	2,000	1,300
14		EECII	Walnut	4000	2,500	1,625
15		EEFFF	Walnut	4300	1,900	1,235
16		CCMM	Pine	4200	2,500	1625
17
18				Type	Weight	Price EU	Price SEA
19
20
21
22			Forecast	Oak	3600	1929	1254
Sheet2

Cell Formulas
Range	Formula
C3	C3	=B2*B3
C4	C4	=B2*B4
F22	F22	=FORECAST($B$2,IF($C$6:$C$16=$B$1,$E$6:$E$16),IF($C$6:$C$16=$B$1,$D$6:$D$16))
G22	G22	=FORECAST($B$2,IF($C$6:$C$16=$B$1,$F$6:$F$16),IF($C$6:$C$16=$B$1,$D$6:$D$16))
Press CTRL+SHIFT+ENTER to enter array formulas.

Works again, thanks!!

 

[AhoyNC]

You're welcome. Thanks for the feedback.