Omit column from Trend formula

[tsolis13]

Hi!

I am using a TREND formula for the first time to predict oil price and sales through the end of 2018 (cells using formula in red). I believe I managed to get the TREND formula to work for column B (Price), but do not think it is populating correctly for column C (Net Vol). I will need to use TREND on column D (Gross Sales) as well. I am assuming the issue lies within the fact that the formula picks up all columns to the left of column A when populating column C and D.

Is there anyway I can filter for just column A (Month) when finalizing the TREND formula for column C and D? Hopefully I'm making sense. Thank you!

[TABLE="width: 305"]
<tbody>[TR]
[TD]Month[/TD]
[TD] Price[/TD]
[TD] Net Vol[/TD]
[TD] Gross Sales[/TD]
[/TR]
[TR]
[TD="align: right"]June17[/TD]
[TD] 44.11[/TD]
[TD] 2,640.71[/TD]
[TD] 116,474.97[/TD]
[/TR]
[TR]
[TD="align: right"]July17[/TD]
[TD] 46.47[/TD]
[TD] 2,516.82[/TD]
[TD] 116,955.79[/TD]
[/TR]
[TR]
[TD="align: right"]Aug17[/TD]
[TD] 48.23[/TD]
[TD] 2,021.35[/TD]
[TD] 97,497.11[/TD]
[/TR]
[TR]
[TD="align: right"]Sep17[/TD]
[TD] 50.42[/TD]
[TD] 2,265.90[/TD]
[TD] 114,235.94[/TD]
[/TR]
[TR]
[TD="align: right"] Oct17[/TD]
[TD] 53.98[/TD]
[TD] 2,034.09[/TD]
[TD] 109,792.86[/TD]
[/TR]
[TR]
[TD="align: right"]Nov17[/TD]
[TD] 60.13[/TD]
[TD] 1,826.73[/TD]
[TD] 109,838.36[/TD]
[/TR]
[TR]
[TD="align: right"]Dec17[/TD]
[TD] 61.70[/TD]
[TD] 1,864.81[/TD]
[TD] 115,061.01[/TD]
[/TR]
[TR]
[TD="align: right"]Jan18[/TD]
[TD] 67.00[/TD]
[TD] 1,825.89[/TD]
[TD] 122,326.94[/TD]
[/TR]
[TR]
[TD="align: right"]Feb18[/TD]
[TD] 64.74[/TD]
[TD] 1,555.79[/TD]
[TD] 100,716.47[/TD]
[/TR]
[TR]
[TD="align: right"] Mar18[/TD]
[TD] 63.55[/TD]
[TD] 1,701.69[/TD]
[TD] 108,140.59[/TD]
[/TR]
[TR]
[TD="align: right"] Apr18[/TD]
[TD] 68.00[/TD]
[TD] 474.38[/TD]
[TD] 32,258.77[/TD]
[/TR]
[TR]
[TD="align: right"]May18[/TD]
[TD]72.22[/TD]
[TD]988.39[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD="align: right"]Jun18[/TD]
[TD] 74.72[/TD]
[TD] 927.54 [/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

I'm not quite sure what you're getting at with the filter commentary. But I've got the trend formula built here with an expanding range. Copy B13 across and down.

It is never a good idea in Excel to have dates listed as they are in your ColA; it is best to make them be Excel Date Serial Numbers so that functions like TREND can work properly. You can always apply a NumberFormat to display them however you like.

Book1
	A	B	C	D
1	Month	Price	Net Vol	Gross Sales
2	6/01/17	44.11	2,640.71	116,474.97
3	7/01/17	46.47	2,516.82	116,955.79
4	8/01/17	48.23	2,021.35	97,497.11
5	9/01/17	50.42	2,265.90	114,235.94
6	10/01/17	53.98	2,034.09	109,792.86
7	11/01/17	60.13	1,826.73	109,838.36
8	12/01/17	61.70	1,864.81	115,061.01
9	1/01/18	67.00	1,825.89	122,326.94
10	2/01/18	64.74	1,555.79	100,716.47
11	3/01/18	63.55	1,701.69	108,140.59
12	4/01/18	68.00	474.38	32,258.77
13	5/01/18	72.22	988.39	80,974.50
14	6/01/18	74.80	835.39	77,053.64
Sheet75

Cell Formulas
Range	Formula
B13	=TREND(B$2:B12,$A$2:$A12,$A13)